Getting a sum by matching multiple criteria?

[joelnichols]

Looking for a formula to match a name in col A to a type in col D and a code in col F , then sum that match from col G. This is similar to the formula I would need , but missing a final variable check from col F.
SUMIF('RH Report'!D:D,A4,'RH Report'!G:G).

Thanks.

 

[Coyotex3]

Unsure if you have Sumifs is available in your version of Excel, but if it is, perhaps something like this would work. This formula assumes your data starts on the second row.

=SUMIFS(G2:G7,A2:A7,A2,D2:D7,D2,F2:F7,F2)

 

[joelnichols]

#NAME? error. Sumifs must be later version. I'm sure there's another option.

 

[Peter_SSs]

I'm sure there's another option.

Try SUMPRODUCT (but don't use whole column references - just make the ranges big enough to cover any amount of data that you might have)

joelnichols_1.xlsm
	D	E	F	G
1	Name		Code	Amount
2	Name 1		A	1
3	Name 2		B	2
4	Name 3		A	3
5	Name 1		B	4
6	Name 2		A	5
7	Name 3		B	6
8	Name 1		A	7
9	Name 2		B	8
10	Name 3		A	9
RH Report

joelnichols_1.xlsm
	A	B	C
3	Name	Code	Total
4	Name 2	B	10
5	Name 3	A	12
Sheet1

Cell Formulas
Range	Formula
C4:C5	C4	=SUMPRODUCT(--('RH Report'!D$2:D$1000=A4),--('RH Report'!F$2:F$1000=B4),'RH Report'!G$2:G$1000)

 

[joelnichols]

Works great. What does the -- in formula do?

Thanks again for the help.

 

[Scott Huish]

Works great. What does the -- in formula do?

Thanks again for the help.

They coerce the TRUE/FALSE values to 1 and 0 respectively

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.