SUMPRODUCT with INDEX and 2x MATCH

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
186
Office Version
  1. 2013
Hi

=IFERROR(INDEX(CWDTWork!$F$4:$F$5000,MATCH(1,INDEX(($D$1=CWDTWork!$G$4:$G$5000)*($A3=CWDTWork!$A$4:$A$5000),0,1),0)),0)

I need to sum all the results from the column and understand SUMPRODUCT may be the answer.

Any help would be appreciated :)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Maybe just a SUMIFS:

=SUMIFS(CWTDWork!$F$4:$F$5000,CWTDWork!$G$4:$G$5000,$D$1,CWTDWork!$A$4:$A$5000,$A3)
 
Upvote 0
Maybe just a SUMIFS:

=SUMIFS(CWTDWork!$F$4:$F$5000,CWTDWork!$G$4:$G$5000,$D$1,CWTDWork!$A$4:$A$5000,$A3)

Hi Eric W, thank you. I think I may not have explained well. Above is my formula that is copied down the column in the second sheet to row 5000, It is this column of results that I wish to sum.

Thanks again
 
Upvote 0
Ah, I see. Is it possible for the $A3 ($A4, etc.) value to appear more than once in the $A$4:$A$5000 column?
 
Upvote 0
Then this might work:

=SUMPRODUCT(SUMIFS(CWTDWork!$F$4:$F$5000,CWTDWork!$G$4:$G$5000,$D$1,CWTDWork!$A$4:$A$5000,$A3:$A5000))
 
Upvote 0
Thanks again Eric, I think I'm still not being clear with you - my apologies.



I am not trying to sum multiple matches, I am trying to do a basic SUM formula of the values of a certain column that are all seperate index/match formulas. A basic SUM formula doesn't work for me.
E.g. C3:C2000 contain individual index/match results which is a number or an IFERROR changes to 0 if no match found. I wish to add up values in that column range?


Thanks
 
Last edited:
Upvote 0
That's what I tried to provide. Have you tried the formula from post 6? The range in red should be the range of values you're matching on.
 
Upvote 0
Ah thank you. Yes, I did try but it returns a zero value.

Thanks again for your help and patience with this
 
Upvote 0
At this point it might require looking as some sample data to get a better idea of what you want. I dummied up some data of my own based on your original formula. It won't match exactly, but it works with your formula. Here's the CWTDWork sheet:


Book1
AFG
1
2
3
4a1dog
5b2dog
6c3dog
7d4cat
8e5cat
9f6cat
10g7mouse
11h8mouse
12i9elephant
13j10elephant
CWTDWork



and given that sheet, here's the lookup sheet:


Book1
ABCD
1dog
266
3a1
4e0
5f0
6i0
7c3
8b2
Sheet6
Cell Formulas
RangeFormula
C2=SUM(B3:B5000)
D2=SUMPRODUCT(SUMIFS(CWTDWork!$F$4:$F$5000,CWTDWork!$G$4:$G$5000,$D$1,CWTDWork!$A$4:$A$5000,$A3:$A5000))
B3{=IFERROR(INDEX(CWTDWork!$F$4:$F$5000,MATCH(1,INDEX(($D$1=CWTDWork!$G$4:$G$5000)*($A3=CWTDWork!$A$4:$A$5000),0,1),0)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.



Your exact formula I used in B3 and copied down. It returns expected results from CWTDWork. The C2 formula is a basic SUM of column B. And then the formula I provided in post 6 in in D2, and as you see, it returns the same answer. So if it doesn't work for you, then something must be different on your sheet. Can you provide some clarification?
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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