SUMPRODUCT with INDEX and 2x MATCH

[horizonflame]

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

 

[Eric W]

Maybe just a SUMIFS:

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

 

[horizonflame]

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

 

[Eric W]

Ah, I see. Is it possible for the $A3 ($A4, etc.) value to appear more than once in the $A$4:$A$5000 column?

 

[horizonflame]

Ah, I see. Is it possible for the $A3 ($A4, etc.) value to appear more than once in the $A$4:$A$5000 column?

No, the values are unique text, `

 

[Eric W]

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))

 

[horizonflame]

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

 

[Eric W]

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.

 

[horizonflame]

Ah thank you. Yes, I did try but it returns a zero value.

Thanks again for your help and patience with this

 

[Eric W]

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
	A	F	G
1
2
3
4	a	1	dog
5	b	2	dog
6	c	3	dog
7	d	4	cat
8	e	5	cat
9	f	6	cat
10	g	7	mouse
11	h	8	mouse
12	i	9	elephant
13	j	10	elephant
CWTDWork

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

Book1
	A	B	C	D
1				dog
2			6	6
3	a	1
4	e	0
5	f	0
6	i	0
7	c	3
8	b	2
Sheet6

Cell Formulas
Range	Formula
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?