Nesting 2 SUMIF formulas in SUBTOTAL

[LuckyJrEskie]

Hi,

I have searched through Mr. Excel and Google, and have reached an impasse. My original formula to add certain values in a list was:


=SUMIF([Total Due], "110") + SUMIF([Total Due], "140")

However, I need this to work as a SUBTOTAL function. I have tried several variations of the below formula with success:

[FONT=&quot]=SUMPRODUCT(SUBTOTAL(109,OFFSET(G11,ROW([Total Due])-ROW(G11),,1)),--([Total Due] = "140"+"110"))


I'd appreciate any suggestions in merging these two separate functions, if possible!


[/FONT]

 

[pgc01]

Hi
Welcome to the board

For ex.:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(Table1[TotalDue],ROW(Table1[TotalDue])-MIN(ROW(Table1[TotalDue])),)),--ISNUMBER(MATCH(Table1[TotalDue],{110,140},0)))

Another option:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(Table1[TotalDue],ROW(Table1[TotalDue])-MIN(ROW(Table1[TotalDue])),))*(Table1[TotalDue]={110,140}))

Remark:

You used "110" which is a string. I guessed that you mean the number 110.

Next time please post a working example, with a small table, the result you got and the result you need.