count # times words appear in column

[Inspector]

I have 5 sheets that have "BAR" in column B in only some
of the cells. I need a formula on SHEET6 A1 to total how
many times "BAR" appears in those columns. This is what I
have:

=SUM('SHEET1'!(B2:B151)+'SHEET2'!(B2:B151)+'SHEET3'!
(B2:B151)+'SHEET4'!(B2:B151)+'SHEET5'!(B2:B151)"BAR")

Obviously this isn't right. Can someone help?

Thanks,
'I'

 

[Peo Sjoblom]

One way

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2:B151"),"BAR
"))

 

[Inspector]

Okay, I tried to be general so as to figure it out myself
but I guess I can't so I must be more detailed. Please see
the following formula:

=SUM('NOV-SAT-8'!(B2:B151)+'NOV-SAT-12'!(B2:B151)+'NOV-
SAT-4'!(B2:B151)"BAR")

If there are no "BAR" then cell needs to be blank.

Thanks,
'I'

 

[Inspector]

i COULD POSSIBLY DO IT FROM ONE SHEET to make it easier
such as:

=SUMPRODUCT(COUNTIF(INDIRECT('NOV RESULTS'!
(B2:B151)"BAR")))

Can you correct any errors in this. Also, if no "BAR"
then cell needs to be blank.

Thanks
'I'

 

[Peo Sjoblom]

Use a summary sheet, put the names of the sheets that you want to count in
let's say
A2:A6

then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A6&"'!B2:B151"),"BAR"))

Use custom format General;General;;

that will hide the zero if there are no BAR found

--

Regards,

Peo Sjoblom

 

[Inspector]

Okay, I guess if the zero is there it's okay. So, I've
decided to take all my info off one sheet rather than five
and I have reworked your formula but it doesn't work. Can
you repair it?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&NOV-SAT-12&"'!

B2:B151"),"BAR"))

Thanks much,
'I'

 

[Peo Sjoblom]

If you have everything in one sheet you only need the countif

=COUNTIF('NOV-SAT-12'!B2:B151,"BAR")

replace 'NOV-SAT-12'! with the sheet's name if needed but include the
apostrophes

 

[Inspector]

-----Original Message-----
If you have everything in one sheet you only need the countif

=COUNTIF('NOV-SAT-12'!B2:B151,"BAR")

replace 'NOV-SAT-12'! with the sheet's name if needed but include the
apostrophes

--

Regards,

Peo Sjoblom





.