Countif

tres · 10 Mar 2009 at 15:10

Dear all

In excel, i have a question...

I have am trying to do a COUNTIF, but based on different criteria range.

I basically want to....

COUNT A1:A50 if B1:B50 = "Bar"

Any ideas?

Thank you!

jack21 · 10 Mar 2009 at 15:22

Hello,

Try this formula and let me know if it works: -

=COUNTIF(A1:A50,"Bar")*(COUNTIF(B1:B50,"Bar")

Josh-A · 10 Mar 2009 at 16:20

Hello,

I think you have to use following formula for the same :-

=SUMPRODUCT(($A$1:$A$50&$B$1:$B$50="Bar")*1)

Put this formula in that column where you want your answer.

Col_M · 10 Mar 2009 at 17:11

So you want a count of the cells in column A where the adjacent cell in column B is "Bar". Is that not the same as counting all the cells in column B where "Bar" is written?

Or do you want to sum all the values of Cells in column A where the corresponding cell in Column B is "Bar"? If so see my equation below

Josh-A said:
Hello,

I think you have to use following formula for the same :-

=SUMPRODUCT(($A$1:$A$50&$B$1:$B$50="Bar")*1)

Put this formula in that column where you want your answer.

That didn't work for me, did you mean =SUMPRODUCT(($B$1:$B$50="Bar")*($A$1:$A$50))

But that will sum not count the cells in A1:A50 where the adjacent cell in the B column is "Bar".

To count the cells in column A adjacent to cells with "Bar" in column B you'd just use =SUMPRODUCT(($B$1:$B$50="Bar")*1) but like I mentioned before that would just do the same as counting the occurrences of "Bar" in column B

Josh-A · 10 Mar 2009 at 17:38

Could you please let me know which excel version you are using and if possible attach a sample file or else let me know the expected output.