Count Ifs Across Tabs

[LadyDi]

I'm trying to use the COUNTIF formula and count up information on several different tabs. However, it keeps giving me the #VALUE! error. This is the formula that I have: =COUNTIF(East:West!F:F, Sheet8!A4). There are five tabs in the workbook that I am trying to count up. Do you know how I can get this to work?

I have spreadsheets from various field managers that I have combined into one workbook. Then I tried to add a Totals tab to count up the data on each of the sheets that the managers sent in, but it isn't working. I eventually, want to get the formulas a little more in depth - to count the categories within each region, using a COUNTIFS formula.

 

[Brianwarnock]

The Range cannot be across worksheets , you will have to code

=COUNTIF(.....)+COUNTIF(....)

Brian

 

[LadyDi]

Thank you for the information.

 

[godsspeed]

im not sure how many ranges you would need to reference, but if theres only a few you could just copy them to the sheet where you want this countif. ...or preform the count if on each sheet, then use the SUM Funciton to add the totals across sheets.

 

[pwbrown]

Can you attach your spreadsheet so I can understand exactly what you are trying to do?
You can change the values or whatever if it's confidential.

 

[Brianwarnock]

Translation of the COUNTIF displayed Is that she wants to count the total of the occurrences of the value in sheet8 cell A4 in the cols F across multiple worksheets, it could be just east and west or east to west with others between.

Brian

 

[NBVC]

Actually, there is a way to use count across multiple sheets without adding multiple countifs together....

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!F:F"),Sheet8!A4))

where TabNames is a named range listing the sheets to include in the summary.

You can replace TabNames with a range reference if you like instead.

e.g.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$X$2:$X$5&"'!F:F"),Sheet8!A4))

where X2:X5 on active sheet contains the sheetnames to include in the count.

 

[Brianwarnock]

I wondered if Sumproduct could be used to achieve this, but would never have come up with that.

Brian