Counting String Instances across sheets

[brandonn]

In QR.xlsx, I have 5 sheets: Agrigate, Q1, Q2, Q3 and Q4.

In Agrigate, I would like to sum the total occurrences of the string TREPAC from across the entire workbook.

I have attempted =COUNTIFS('[QR.xlsx]Q1 13'!$A:$D, "TREPAC", '[QR.xlsx]Q2 13'!$A:$D, "TREPAC", '[QR.xlsx]Q3 13'!$A:$D, "TREPAC", '[QR.xlsx]Q4 13'!$A:$D, "TREPAC")
which produced a value of 0.

I also tried a similar thing that was =sum( =count(x), =count(x), ... ) but that was a total mess.

Can anyone check my syntax on the above line?

Anybody know of a better formula to use, or a better way to use the counifs formula?

[JBeaucaire]

Wouldn't it simply be a sum of 4 COUNTIF() formulas?

=SUM(COUNTIF('[QR.xlsx]Q1 13'!$A:$D, "TREPAC"), COUNTIF('[QR.xlsx]Q2 13'!$A:$D, "TREPAC"), COUNTIF('[QR.xlsx]Q3 13'!$A:$D, "TREPAC"), COUNTIF('[QR.xlsx]Q4 13'!$A:$D, "TREPAC"))

[brandonn]

ok, that worked, exactly as you typed it. thanks a lot.

for edification, can you explain why countifs produced a value of 0?

[JBeaucaire]

COUNTIFS() is an inclusive function, all of the tests within must be true for the count to go up. What you were trying to do was evaluate each sheet separately, a completely different idea.

A good use of COUNTIFS would be to count how many rows in a single data table had:

1) "Cat" in column A
AND
2) "Dog" in column B
AND
3) "Bird" in column C

=COUNTIFS(A:A, "Cat", B:B, "Dog", C:C, "Bird")

So, in this sample data, the answer would be ONE, because only row 3 has all 3 tests as true at the same time:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.