Tricky Sum(IF(

[Joe Galvan]

Hi Everyone,

I am unable to download that Spreadsheet Genie, so I will do my best...


This is what I have for the formula so far...

Sum(if(Claim Detail!$O$9:$O$11000>=$D$1,if(Claim Detail!$O$9:$O$11000<=$E$1,if(Claim Detail!$M$9:$M$11000=$B$42


The last if is what I am having trouble with.... I need the last "IF" to compare a list of values to a row on the Claim Detail page...


Values Example (There is a list of 20 different values)
A
B
C
D
E

So the last "IF" statment in the above formula needs to match column K on the Claim Detail tab to any one of the Values... So after the previous "IF's" are satisfied, the last "IF" could match A or B or C etc...and then finally sum column BQ on the Claim Detail tab...


Make Sense???


Thank you,
JOE

 

[jasonb75]

Hi Joe, hard to make proper sense of, but if your data will fit on screen try:-

ScreenPrint
Open Paint
Paste (in edit menu)
Save as JPEG
Insert the image to the forum.

Then people can see your sheet to make sense of it.

Hope this helps

 

[Sal Paradise]

I think you want SUMPRODUCT()

=SUMPRODUCT((Claim Detail!$O$9:$O$11000>=$D$1)*(Claim Detail!$O$9:$O$11000<=$E$1)*(Claim Detail!$M$9:$M$11000=$B$42)*(Claim Detail!$K$9:$K$11000={"A","B","C","D","E"})*Claim Detail!$BQ$9:$BQ$11000)

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(
   IF(ClaimDetail!$O$9:$O$11000>=$D$1,
   IF(ClaimDetail!$O$9:$O$11000<=$E$1,
   IF(ClaimDetail!$M$9:$M$11000=$B$42,
   IF(ISNUMBER(MATCH(ClaimDetail!$K$9:$K$11000,ValuesList,0)),
     ClaimDetail!$BQ$9:$BQ$11000)))))

Note 1. ClaimDetail (no space) instead of Claim Detail (with space) allows avoiding quotes around the sheet name in the formulas.
Note 2. ValuesList is the name of the range housing your 20 or so values.

Hi Everyone,

I am unable to download that Spreadsheet Genie, so I will do my best...


This is what I have for the formula so far...

Sum(if(Claim Detail!$O$9:$O$11000>=$D$1,if(Claim Detail!$O$9:$O$11000<=$E$1,if(Claim Detail!$M$9:$M$11000=$B$42


The last if is what I am having trouble with.... I need the last "IF" to compare a list of values to a row on the Claim Detail page...


Values Example (There is a list of 20 different values)
A
B
C
D
E

So the last "IF" statment in the above formula needs to match column K on the Claim Detail tab to any one of the Values... So after the previous "IF's" are satisfied, the last "IF" could match A or B or C etc...and then finally sum column BQ on the Claim Detail tab...


Make Sense???


Thank you,
JOE