Need help with INDEX,MATCH & SUM formula

[rizmomin]

Hello:

Please refer to the attached sheet with sample data.
Sheet1 has some data which is used in Sheet2 to Summarize the quantity of each item used.

I am using formula

Please Login or Register  to view this content.

The result i wanted to see was -2+7=+5 in cell D5 of sheet2.
I am not sure if this formula is wrong.
Please help correct the formula which is in cell D4:D

Please let me know if any questions.

Thank you

Riz Momin

[AlKey]

Try this: Enter in D4 and drag formula down

=SUMPRODUCT((Sheet1!$D$3:$D$5=A4)*(Sheet1!$A$3:$A$5=Sheet2!$A$1)*(Sheet1!$G$3:$G$5))

[Richard Buttrey]

Hi If you're trying to sum by date and Item, # then why not simply

Please Login or Register  to view this content.

[martindwilson]

you could just use sumif
=SUMIF(Sheet1!$D$3:$D$5,A4,Sheet1!$G$3:$G$5)

[Tony Valko]

Try it like this...

=SUMIFS(Sheet1!$G$1:$G$20,Sheet1!$E$1:$E$20,D$2,Sheet1!$D$1:$D$20,$A5,Sheet1!$A$1:$A$20,$A$1)

[Pete_UK]

Even though that is an array formula, MATCH will still only return the first row where it finds a match, and so the formula will only return the first value where there is a match. I think you want SUMPRODUCT or SUMIFS (would be quicker) like this:

=SUMIFS(Sheet1!$G$1:$G$20000,Sheet1!$E$1:$E$20000,D$2,Sheet1!$D$1:$D$20000,$A4,Sheet1!$A$1:$A$20000,$A$1)

then copy down.

Hope this helps.

Pete

EDIT: Wow, I didn't expect so many people to be active on a Saturday evening!! (Well, here in the UK it is)

Pete

[rizmomin]

Hi Pete_UK:

Your formula works great.
How would i modify so to ignore if result is zero.
Basically, i only want to see if result is not zero

Thank you

Riz Momin

[AlKey]

This will show blanks instead of 0.

=IF(SUMPRODUCT((Sheet1!$D$3:$D$5=A4)*(Sheet1!$A$3:$A$5=Sheet2!$A$1)*(Sheet1!$G$3:$G$5))=0,"",SUMPRODUCT((Sheet1!$D$3:$D$5=A4)*(Sheet1!$A$3:$A$5=Sheet2!$A$1)*(Sheet1!$G$3:$G$5)))

[rizmomin]

Hi AlKey:

Thanks a lot to both of you for great help.

Riz Momin

[AlKey]

You're Welcome!

[Pete_UK]

Yeah, basically you could do:

=IF(your_formula=0,"",your_formula)

Another approach, though, is to use conditional formatting, set such that if the value in the cell is zero then the foreground colour is set to the background colour. The zero is still there, it's just that you don't see it.

A third approach is to apply a Custom Format to the cells of:

General;General;

This also prevents the zero from being displayed.

Hope this helps.

Pete

[rizmomin]

Hi Pete_UK:

You are super exceler!!!!!!!!!!!!!!

Thanks

Riz Momin

[Tony Valko]

Try this tweak to Pete's formula...

=IFERROR(1/(1/SUMIFS(Sheet1!$G$1:$G$20000,Sheet1!$E$1:$E$20000,D$2,Sheet1!$D$1:$D$20000,$A4,Sheet1!$A$1:$A$20000,$A$1)),"")