# Tricky Sum(IF(

#### Joe Galvan

##### Board Regular
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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)

##### MrExcel MVP
Control+shift+enter, not just enter:

Code:
``````=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

Replies
0
Views
462
Replies
25
Views
2K
Replies
1
Views
313
Replies
0
Views
247
Replies
8
Views
388

1,191,166
Messages
5,985,051
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back