Thread: Formula COUNTIF Multiple Ranges with One Criteria

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.