IF THEN statements

[perale]

I have 2 columns A & B. I want Xcel to first look at Column A and IF it
contains the number 1, then go to Column B and count how many A-1's there
are. This is my formula - it's giving me a FALSE answer but I need a number.


=IF($A$2:$A$174="1",COUNTIF($B$2:$B$174,"A-1")

 

[Sean Timmons]

Are you wanting to do a lookup for the number one in any cell n A???

=IF(ISERROR(VLOOKUP(1,A:A,1,0)),"",COUNTIF($B$2:$B$174,"A-1"))

 

[Mike H]

Hi,

I'm not entirely sure I understand what you mean by A-1 and have assumed you
mean text.

=SUMPRODUCT((A2:A174=1)*(B2:B174="A-1"))

Mike

 

[Rick Rothstein]

Try this formula instead...

=SUMPRODUCT(($A2:$A$174=1)*($B$2:$B$174="A-1"))

 

[JLatham]

Try
=SUMPRODUCT(--($A$2:$A$174=1),--($B$2:$B$174="A-1"))
That assumes that the values in column A are actual number, if they are text
then
=SUMPRODUCT(--($A$2:$A$174="1"),--($B$2:$B$174="A-1"))

 

[perale]

Yes I am i.e. I have groups 1 - 13 listed in Column A. I want Xcel to go to
Column A, find any Group 1 and if that criteria exists, go to Column B and
see if there are any A-1's and if there are give me a total of how many there
are. In other words how many people in Group 1 fall in the A-1 category?

 

[perale]

Thanks Rick. That worked.

Try this formula instead...

=SUMPRODUCT(($A2:$A$174=1)*($B$2:$B$174="A-1"))

--
Rick (MVP - Excel)




.