Using IF and COUNTIF together

**extremecorvette** · 12-02-2011, 01:36 PM

I have this formula working for just the one cell but I would like it to work for everything the the column from A24:A1500 and J24:J1500. But for some reason when I change it to a range it errors out.

I'm using Office 2010

PHP Code:


=IF(A24="Atlanta", COUNTIF(J24,"Bankrupt"),0)

**Ron Coderre** · 12-02-2011, 01:49 PM

Try something like this:

=SUMPRODUCT((A24:A1500="Atlanta")*(J24:J1500="Bankrupt"))

Is that something you can work with?

**Whizbang** · 12-02-2011, 02:24 PM

Ron's method is better, in my opinion, but the formula to use if you wanted to continue using COUNTIF and IF would be

=COUNTIF(IF(A24:A1500="Atlanta", J24:J1500),"Bankrupt")
(Note: Confirm with CTRL+SHIFT+ENTER, not just ENTER)

For more information on SUMPRODUCT, I recommend reading this article:
http://xldynamic.com/source/xld.SUMPRODUCT.html

**Whizbang** · 12-02-2011, 03:36 PM

Oops. I forgot COUNTIF doesn't work with Arrays.

=SUM(IF(IF(A24:A1500="Atlanta", J24:J1500)="Bankrupt",1,0))
Confirmed with CTRL+SHIFT+ENTER

**extremecorvette** · 12-02-2011, 03:50 PM

Ron's works great. I tried the COUNTIF but ran into problems.

Is there a way to put a range in like Atlanta, Dallas, Brazil, Cedar Rapids in as a range to count?

**jeffreybrown** · 12-02-2011, 04:05 PM

How about...

=SUMPRODUCT((A24:A1500={"Atlanta","Dallas","Brazil","Cedar Rapids"})*(J24:J1500="Bankrupt"))

**Haseeb Avarakkan** · 12-02-2011, 04:26 PM

Originally Posted by extremecorvette

I'm using Office 2010

Use COUNTIFS, which is faster than SUMPRODUCT.

=SUM(COUNTIFS(A:A,{"Atlanta","Dallas","Brazil","Cedar Rapids"},J:J,"Bankrupt"))

Or, enter all the Keywords in a range & use like,

=SUMPRODUCT(COUNTIFS(A:A,Keyword_Range,J:J,"Bankrupt"))