Finding the Sum using multi criteria Index & Match

Defratos

New Member
Joined
Jan 10, 2013
Messages
15
Hi,

I am currently trying using a table in Sheet1 (similar to whats below) to summarize the data on Sheet 2 which has data over more than one line. The formula i am using is

=IFERROR(INDEX(Sheet2!$B$2:$D$400,MATCH(1,(Sheet2!$B$2:$B$400=Sheet1!$C$5)*(Sheet2!$C$2:$C$400=Sheet1!B6),0),3),0)

CategoryEnglishFrenchSpanish
Complete pack3902
Complete pack (excl USB)
Assembled complete pack
Assembled complete pack (exl USB)
Folders
A3 Placemats
Invitation to grow with us
Support for member firms
USB

<tbody>
</tbody>


Sheet 2 looks similar to this:

DateLanguageTypeNumber
23/10/2014EnglishComplete pack39
23/10/2014SpanishComplete pack2
23/10/2014EnglishComplete pack200
23/10/2014EnglishComplete pack -20

<tbody>
</tbody>


My problem however is that the formula is just picking up the first number (39 for English Complete pack). I would like the formula to Sum 39+200-20 for English Complete pack.

Please let me know how i can fix this.
Thanks (i would upload the excel if i could figure out from where :S)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try:

=SUMPRODUCT(--(Sheet2!$B$2:$B$400=Sheet1!C$5),--(Sheet2!$C$2:$C$400=Sheet1!$B6),Sheet2!$D$2:$D$400)
 
Upvote 0
Try:

=SUMPRODUCT(--(Sheet2!$B$2:$B$400=Sheet1!C$5),--(Sheet2!$C$2:$C$400=Sheet1!$B6),Sheet2!$D$2:$D$400)


Thanks Steve, that worked perfectly. I am facing one more problem in the same area which i have a feeling will just need a minor adjustment.

In Sheet 2, to the right of the table stated above is another table similar to the one below.

FGHIJKL
DateNameRoleCountryLanguageTypeNumber
24/10/2014

<tbody>
</tbody>
BobInternUnited KingdomEnglishComplete pack-200

<tbody>
</tbody>


I would also like to incorporate the -200 in the formula (using the English/Complete pack) criteria on top of the formula you mentioned. So just to be clear it would be taking the data from two tables (one that is always positive and one that is always negative). Thanks
 
Upvote 0
You just need to add another sumproduct formula to the original one basically the same formula but using JKL columns instead.

=SUMPRODUCT(blah blah) + SUMPRODUCT(blah blah)
 
Upvote 0
Hmm..

I was trying that. The formula i have so far is :

=SUMPRODUCT((Sheet2!$B$2:$B$400=Sheet1!C$5),(Sheet2!$C$2:$C$400=Sheet1!$B6),Sheet2!$D$2:$D$400)+SUMPRODUCT((Sheet2!J2:J411=Sheet1!C5),(Sheet2!K2:K411=Sheet1!B6),Sheet2!L2:L400)

It should be pulling 39 and -200 and end up as -161, however it just comes up with #VALUE.
 
Last edited:
Upvote 0
1. It's a case for SumIfs (available op post-2003 systems), not for SumProduct which is expensive:

Let A:D of Sheet2 house the data and A:D of Sheet1 house the processing...

In B2 of Sheet1 enter, copy across, and down:

=SUMIFS(Sheet2!$D:$D,Sheet2!$B:$B,B$1,Sheet2!$C$C,$A2)

2. Would you elaborate on your second question? What are the source, the destination, and the criteria?
 
Upvote 0
Nvm, i am not sure how i fixed it, but i used

=SUMPRODUCT(--(Sheet2!$B$2:$B$400=Sheet1!C$5),--(Sheet2!$C$2:$C$400=Sheet1!$B6),Sheet2!$D$2:$D$400)+SUMPRODUCT(--(Sheet2!J2:J400=C5),--(Sheet2!K2:K400=B6),Sheet2!L2:L400)

Thanks guys!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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?

Disable AdBlock

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
Back
Top