SUMPRODUCT with mutliple criteria?

[KatDaw]

Hi,
I'm hoping someone can help with this. I'm trying to use SUMPRODUCT with a table to sum the values which match my criteria.

I've created the table ("FeeTable") and using a cell reference, I need to locate the first criteria ("DL Renew") then sum all the columns to the right of the second criteria ("September").

I've tried using INDEX MATCH MATCH, but that only provides the data for September, it ignores the remaining columns.

Below is a screen shot of the information in the sample file attached.



I appreciate any assistance!
Thanks,
Kathy

 

[bosco_yip]

Try,

In B14, enter formula :

=SUM(OFFSET(FeeTable[[#Headers],[FEE TABLE]],MATCH(B10,FeeTable[FEE TABLE],0),MATCH(LEFT(B11,FIND("to",B11)-2),FeeTable[[#Headers],[April]:[March]],0),,MATCH(MID(B11,FIND("to",B11)+3,9),FeeTable[[#Headers],[April]:[March]],0)-MATCH(LEFT(B11,FIND("to",B11)-2),FeeTable[[#Headers],[April]:[March]],0)+1))

Regards
Bosco

 

[Khalid NGO]

HI,

May be this {array formula}

=SUM(IFERROR((INDIRECT(D11))*(A:A=INDIRECT(D10)),0))

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Note: it uses full column reference, it may slow down your sheet.

Regards,

 

[KatDaw]

Try,

In B14, enter formula :

=SUM(OFFSET(FeeTable[[#Headers],[FEE TABLE]],MATCH(B10,FeeTable[FEE TABLE],0),MATCH(LEFT(B11,FIND("to",B11)-2),FeeTable[[#Headers],[April]:[March]],0),,MATCH(MID(B11,FIND("to",B11)+3,9),FeeTable[[#Headers],[April]:[March]],0)-MATCH(LEFT(B11,FIND("to",B11)-2),FeeTable[[#Headers],[April]:[March]],0)+1))

Regards
Bosco

Thank Bosco,
That's quite an interesting formula, but I would need it to be a bit more dynamic, as the range for the months can vary depending on the row in question.
For example, the months could be January to March for one of the other rows.

I'm able to reference the starting month, so any suggestions on how to make it dynamic would be appreciated.
Thanks
Kathy

 

[bosco_yip]

Thank Bosco,
That's quite an interesting formula, but I would need it to be a bit more dynamic, as the range for the months can vary depending on the row in question.
For example, the months could be January to March for one of the other rows.

I'm able to reference the starting month, so any suggestions on how to make it dynamic would be appreciated.
Thanks
Kathy

Try,

1] To select "Name" and "Month", by clicking Validation dropdown list in B10, B11 and D11

2] "Total" In B14, enter formula :

=SUM(OFFSET(FeeTable[[#Headers],[FEE TABLE]],MATCH($B$10,FeeTable[FEE TABLE],0),MATCH($B$11,FeeTable[[#Headers],[April]:[March]],0),,MATCH($D$11,FeeTable[[#Headers],[April]:[March]],0)-MATCH($B$11,FeeTable[[#Headers],[April]:[March]],0)+1))

3] See attached file and the running custom formatting for the months %

Regards
Bosco

 

[KatDaw]

Thanks Bosco,
With a little tweaking, I've made this work in my larger model.

Thanks again for your help!!
Regards,
Katy