Thread: Formula COUNTIF Multiple Ranges with One Criteria

Hi Kat & Welcome to the board,

Maybe...

=COUNTIF(X:X,"Pending")+COUNTIF(AD:AD,"Pending")+COUNTIF(AJ:AJ,"Pending")+COUNTIF(AP:AP,"Pending")+C OUNTIF(AV:AV,"Pending")

If you don't have "Pending" appearing within the intervening columns you can use a single contiguous range:

=COUNTIF(Data!X:AV,"Pending")

This is the formula I used that finally worked! Whew!

=COUNTIF('Complex Claim Denials'!X2:X5000,"Pending")+COUNTIF('Complex Claim Denials'!AD2:AD5000,"Pending")+COUNTIF('Complex Claim Denials'!AJ2:AJ5000,"Pending")+COUNTIF('Complex Claim Denials'!AP2:AP5000,"Pending")+COUNTIF('Complex Claim Denials'!AV2:AV5000,"Pending")

Now I'm trying to do the same thing, but using the SUMIF function with the same columns and criteria except adding the total dollar amounts for each column that says "Pending." I tried doing a similar formula, but I can't seem to get it to work.

This is the formula for a single range.

=SUMIF('Complex Claim Denials'!X2:X5000,"Pending",'Complex Claim Denials'!Q2:Q5000)

How do I turn that into multiple ranges like the COUNTIF formula previously?

Thank you for all your help!

Hi Kat,
I'm not at my computer right now and won't be for about an hour.

Are all the totals coming from the same column?

I have a hunch you will be able to use:

=SUMIF('Complex Claim Denials'!X2:AV5000,"Pending",'Complex Claim Denials'!Q2:AO5000)

and would suggest trying before discounting...

the above assumes you have identical 5 tables to aggregate (conditionally) where column to sum is 7 columns to left of criteria column in each table.

e4excel, yes you can embed SUMIF into SUMPRODUCT but there should really be no need to. I know you like SUMPRODUCT but you must remember it performs hideously.

e4excel, yes you can embed SUMIF into SUMPRODUCT but there should really be no need to. I know you like SUMPRODUCT but you must remember it performs hideously.

YEah...Sheepishly Yes

Not only b'cos of that but also it actually saved a lot of lines of code in the other query..

I was thinking of that to be used in this as I was not aware of this feature of SUMPRODUCT till then..

=SUMPRODUCT(SUMIF(INDIRECT("'Complex Claim Denials'!"&RANGE1),"Pending",INDIRECT("'Complex Claim Denials'!"&RANGE2)))

where
RANGE1 would be lets say begin from XY2 TILL XY6 and contain the values as RANGE1 = XY2:XY6
XY2 = X2:X5000
XY3 = AD2:AD5000
XY4 = AJ2:AJ5000
XY5 = AP2:AP5000
XY6 = AV2:AV5000

Similarly, you can have a column XZ starting from XZ2 TILL XZ6 and which contains the SUMMING RANGE COLUMN NAMES as shown above.
So RANGE2 = XZ2:XZ6

Without the sample book its difficult for me to try as I am no DOnkeyOte..the Great

Warm REgards
e4excel

The totals for the sum of amount of money are coming from the same column. It's the "Pending" criteria that is coming from several different columns. Does that make sense?

Hi Kat,

Do you have a sample workbook to upload of what you have and what you desire?

Did you try DonkeyOte's suggestion?

Jeff, I think I jumped the gun ?

By the sounds of it the values to SUM are always in Q but the criteria are in separate columns; ie the value Q is a multiplier of some kind.

If so, perhaps:

=SUMPRODUCT((MOD(COLUMN('Complex Claim Denials'!$X2:$AV2)-COLUMN('Complex Claim Denials'!$X2),6)=0)*('Complex Claim Denials'!$X$2:$AV$5000="Pending")*'Complex Claim Denials'!$Q$2:$Q$5000)

The above will generate #VALUE! errors should Q2:Q5000 contain non-numerics (eg "apple")

@Luke,

It's amazing how you guys can see these things without the visual. I'm very visual so a lot of time not quite catching on...Thanks...

Hi Jeff,

Here is a sample workbook - extremely basic to what I have, but it serves the purpose!

Basically, it is five columns of data (not next to each other) and one column of dollar amounts.

I need to add the five columns of data where it says pending in the column and their corresponding dollar amounts in the sixth column for a total dollar amount.

Thanks for all your help!

Hi Kat,

I think the simplest way, in B2...

=IF(COUNTIF(D2:P2,"Pending")>0,A2,"") and drag down.

Then just sum column B

...If you want to do it all in one go, then...

=SUMIF($D$2:$P$393,"Pending",$A$2:$A$393)