count # times words appear in column

  • Thread starter Thread starter Inspector
  • Start date Start date
I

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'
 
One way

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2:B151"),"BAR
"))
 
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'
 
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'
 
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
 
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&"'!
Click to expand...
B2:B151"),"BAR"))

Thanks much,
'I'
 
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
 
-----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





.
Click to expand...
 
Back
Top