Countifs with Multiple criteria across multiple sheets

[Maxwelljohn]

Hi, I’m trying to build a dashboard that needs to countifs across multiple sheets based on what the colleague selects in a drop down.

=SUMPRODUCT(COUNTIF(Sheet1!A1:A100, Criteria1) + COUNTIF(Sheet2!A1:A100, Criteria1), COUNTIF(Sheet1!B1:B100, Criteria2) + COUNTIF(Sheet2!B1:B100, Criteria2))

I tried this but it kept returning errors. I also tried adding an additional criteria to each sheet and using Countifs as a sum but again it was corrupting the output.

This dashboard also contains averageifs and sumifs, so any support with all of these would be a massive help.

 

[etaf]

what does COUNTIF(Sheet1!A1:A100, Criteria1) produce and each of the other countif()
test each one and see if it returns an error

any errors already on the sheets

this should work
=COUNTIF(Sheet1!A1:A100, Criteria1) + COUNTIF(Sheet2!A1:A100, Criteria1) + COUNTIF(Sheet1!B1:B100, Criteria2) + COUNTIF(Sheet2!B1:B100, Criteria2)
if not found - then should get a zero

you have a , instead of +

=SUMPRODUCT(COUNTIF(Sheet1!A1:A100, Criteria1) + COUNTIF(Sheet2!A1:A100, Criteria1), COUNTIF(Sheet1!B1:B100, Criteria2) + COUNTIF(Sheet2!B1:B100, Criteria2))