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")+COUNTIF(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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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)

This is the formula I came up with for the second part.

=SUMIF('Complex Claim Denials'!X2:AV5000,"Pending",'Complex Claim Denials'!O2:O5000)

It seems to work. Since it is only specific columns in that range that contain the word "Pending," it only picks those words and their corresponding dollar amounts. Or at least that's what it seems like it's doing!

Thank you for everyone's help! It's greatly appreciated.

Thanks Jeff for the idea!

Hi Kat,

Glad you've got it working

Please mark the thread as solved if complete

Kat,

Originally Posted by katcollins

=SUMIF('Complex Claim Denials'!X2:AV5000,"Pending",'Complex Claim Denials'!O2:O5000)

It seems to work. Since it is only specific columns in that range that contain the word "Pending," it only picks those words and their corresponding dollar amounts. Or at least that's what it seems like it's doing!

The O2:O5000 will in fact default "behind the scenes" to O2:AM5000 - in SUMIF function the dimensions of the summation range will match that of the criteria range (25 columns wide)
i.e. this formula is equivalent approach to that made in post #6

If the dollar amounts exist only in Col O then this approach will not work.

We can prove this using the last sample file - if you use:

=SUMIF(D2:P393,"Pending",A2:A393)

you will get 191,980.6 versus the correct amount of 300,372.25

This is because the above will in effect only sum Col A where Col D is Pending. Where Col G is Pending the above will in fact be summing Col D - this is not obvious but is the way SUMIF works.

Jeff's previous suggestion circumvents the above issue by making the Criteria Range and Summation Range the same size - i.e. 1 column wide. I would suggest you revert to this approach.

Originally Posted by DonkeyOte

Kat,



The O2:O5000 will in fact default "behind the scenes" to O2:AM5000 - in SUMIF function the dimensions of the summation range will match that of the criteria range (25 columns wide)
i.e. this formula is equivalent approach to that made in post #6

If the dollar amounts exist only in Col O then this approach will not work.

We can prove this using the last sample file - if you use:

=SUMIF(D2:P393,"Pending",A2:A393)

you will get 191,980.6 versus the correct amount of 300,372.25

This is because the above will in effect only sum Col A where Col D is Pending. Where Col G is Pending the above will in fact be summing Col D - this is not obvious but is the way SUMIF works.

Jeff's previous suggestion circumvents the above issue by making the Criteria Range and Summation Range the same size - i.e. 1 column wide. I would suggest you revert to this approach.

I tried Jeff's approach. The problem is when I used his formula, it returned a #Value error instead of calculating the formula. But it's possible that I'm not entering it right. Can you break it down into very basic language for me? I don't have an accounting/statistics mind and yet, this the task I'm trying to do! Ugh.

I have attached your earlier sample file with an illustration of Jeff's approach; see Column R for Pending test and T2 for final Summation.

You can achieve the same without use of Column R, however, it's an elegant and efficient approach.

808289_katcollins.xls

Originally Posted by DonkeyOte

I have attached your earlier sample file with an illustration of Jeff's approach; see Column R for Pending test and T2 for final Summation.

You can achieve the same without use of Column R, however, it's an elegant and efficient approach.

Attachment 135777

This looks great except the problem is that the "pending" is fluid in the columns depending on the results that come back from insurance appeals. This means that "pending" can change to other terms and it would no longer be "pending." How do I achieve the same without the use of column R since column R won't work for my spreadsheet?

This will give you the same result.

=SUMPRODUCT((D2:P393="pending")*A2:A393)

NOTE: This will sum any cell ="pending" in D2:P393

Originally Posted by Haseeb A

This will give you the same result.

=SUMPRODUCT((D2:P393="pending")*A2:A393)

NOTE: This will sum any cell ="pending" in D2:P393

This returns me a value of "$0.00" It doesn't sum any of the monetary amounts. Does it matter that I'm using Excel 2007? I don't know if that's different or not.

That means, there are NO cells in D2:P393 = Pending OR A2:A393 = 0

See the attached B1 is the answer.

Here is a sample of exactly what I'm trying to do. On the SUMMARY page, I am trying to calculate a formula to answer question number 4a. (ignore the other formulas, etc. I deleted confidential info for this sample so it's not calculating those correctly).

To answer question 4a, I have to take all of the cells on the "Complex Claims Denials" worksheet that say "Pending" in columns K, Q, W, AC, and AI and calculate the sum of their monetary values that correspond in column O. This number has to be fluid in that the "Pending" will change as appeals are denied, upheld, overturned, etc. and more rows will be added over time as records come in. So it cannot be a static formula. This means that I need ALL of the column for K, Q, W, AC, and AI to be included in the formula- not just the specific cells that say "Pending" currently.

I tried SUM and SUMIF and SUMIFS formulas, but they are running into problems and not calculating correctly. I believe it is because the criteria is a word (Pending) and not a numeric value? Or maybe I'm not using multiple ranges correctly?

Does this make sense? Thank you so much for working so hard on this!

You have validation list in most of the columns, so assume 'Pending' will never have other columns,, If so try this.

=SUMPRODUCT(('Complex Claim Denials'!K2:AI403="pending")*'Complex Claim Denials'!B2:B403)

You can use dynamic range, so whenever data add/delete will update it automatically

Originally Posted by Haseeb A

You have validation list in most of the columns, so assume 'Pending' will never have other columns,, If so try this.

=SUMPRODUCT(('Complex Claim Denials'!K2:AI403="pending")*'Complex Claim Denials'!B2:B403)

You can use dynamic range, so whenever data add/delete will update it automatically

How do I make it a dynamic range since I will need it to update automatically?

See the attached.