Count & What IF Statement

[rhjake23]

Hello.

I am trying to do the following.

If range A1:A1000 = "ABC" then count range B1:B1000

FYI there are formulas in range B1:B1000
so when in Excel and I highlight B1:B1000 i get a count of 1000..not sure why ....maybe because each cell has a formula ?
Please Help !! Thank you.

 

[Michael M]

Try

=SUMPRODUCT(--(A1:A1000="ABC"),(B1:B1000))

 

[rhjake23]

Thanks for the reply... I get a "0" result...

 

[Michael M]

IS ABC the only text in the cell OR is it part of a string?
Did you copy the formula exactly as written ?

 

[Michael M]

If you are only counting a range , why not simply use...

=COUNTIF(A1:A1000,"ABC")

 

[rhjake23]

Hi...
no abc is not the only string...
lets say a1:a1000 could be "abc" "def" "ghi"
based on that ...i want the count in b1:b1000

not sure if i am making any sense

 

[Michael M]

So, if you put your text in a seperate cell, say D1
then use

=COUNTIF(A1:A1000,D1)

You are only counting occurences of the text, so it shouldn't need to be any other column

 

[rhjake23]

ok great...
now i'm getting a result...which is good..
the returned result is a sum...and not a count
getting close....

 

[Michael M]

Can you use the XL2BB download (see my tag) to provide a sample of your data and the expected result ?
I can't see how you can get a SUM returned from the formula I provided ?

 

[rhjake23]