Expand SUMPRODUCT(SUMIF(INDIRECT)) Formula

[chrisguk]

Hi,

I have this formula which works perfect for my needs. As you can see it looks up the values in the sheets named Jan, Feb , Mar etc etc. The Criteria H$5:S$5 is as follows:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

fx: =IFERROR(T6-SUMPRODUCT(SUMIF(INDIRECT("'"&H$5:S$5&"'!"&"A$4:A$30");B6;INDIRECT("'"&H$5:S$5&"'!"&"D$4:D$30")))

A	B
Item	Type of invoice
item1	Invoice
item2	UIS
item3	ID

<tbody>
</tbody>

What I need is for the formula only to produce a result if the column B = Invoice

If none of this makes sense I can post here a snip of the sheets, if you can tell me how I can do that in a formatted way?

Many thanks in advance
Chris

 

[Aladin Akyurek]

Try...

=IFERROR(T6-SUMPRODUCT(SUMIFS(INDIRECT("'"&H$5:S$5&"'!"&"D$4:D$30");
    INDIRECT("'"&H$5:S$5&"'!"&"A$4:A$30");B6;INDIRECT("'"&H$5:S$5&"'!"&"B$4:B$30");"invoice"))

 

[chrisguk]

Thank you,

Your solution works perfect and it also helped me understand SUMIFS better. This is a much more flexible method.

 

[Aladin Akyurek]

Thank you,

Your solution works perfect and it also helped me understand SUMIFS better. This is a much more flexible method.

You are welcome and thanks for the feedback.