0

I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter to count or array_constraint is not working as well as i expect.

enter image description here

In the above data in Column B to K, I am creating the summary in Columns M to Q.

For Site Exception review(M21) row >> i am using the below formula to return the count of statuses for the type in the quarter and the site. The problem is for 2023 Q2, there is no Site exception review, but since the filter part returns a NA error, thats considered as 1 count, making the answer wrong. Is there anyway for me to overcome the issue when there is no matching to the filter, instead of the NA being count as one, it should show as 0.

Site Exception review =COUNTA(FILTER($K$18:$K$62,$B$18:$B$62=$N$19,$F$18:$F$62=N$20,$G$18:$G$62=$M$21))

For Match Module(M22) row >> i am using the below formula to return as "Yes" if its present, if not "No". Similar to Site exception, when the filter return NA error its getting count as 1. Therefore making the cell as "Yes", for 2023 Q3 eventhough there are no match modules for that quarter.

Match Module =IF(COUNTA(FILTER(K18:K56,B18:B56=N19,F18:F56=N20,G18:G56=M22))>0,"Yes","NO")

For Ad Hoc Presentation(M24) row >> I am using the below formula to pull the status of the Ad Hoc Presentation type. But i am looking for the status of the last value, but the formula is giving me the first value. For example, in 2023 Q1, i am expecting the value in N24 to be "Socialized", but its showing as "Draft Completed" as thats the first value. Is there any way to pull the last value instead of the first one?

Ad Hoc Presentation =IFERROR(array_constrain(FILTER($K$18:$K$56,$B$18:$B$56=$N$19,$F$18:$F$56=N$20,$G$18:$G$56=$M$24),1,1),0)

Do let me know if any other information is required.

1
  • 1
    While an image might help, when asking questions about Google Sheets that are related to data handling, like using formulas, please add the data from the referenced cells and ranges as text as well for the expected result. Also, it's important to describe the spreadsheet structure and relevant settings like the spreadsheet locale (country). Feb 27 at 16:23

1 Answer 1

0

To get the numeric part of the report, use query(), like this:

=query( 
  A1:K; 
  "select G, count(G) 
   where B contains '" & N19 & "' 
   group by G 
   pivot F"; 
  1 
)

...where cell N19 contains a text string like Paris.

Set additional where...and clauses to columns G and K as necessary.

See query().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.