Sumifs with list and criteria

[Rmcano]

Hi all,

I am trying to calculate the number of sales by customer type across a given period, and am having issues with the third criteria of my sumifs formula - specifically how to type it.

• First criterion - Year of sale
• Second criterion - Country
• Third criterion - Customer name is one of the names which is classified in another list as being 'Priority 1 customer'

The issue is I have a table will all customer names, and over the years these change from priority 1 - 15. I wish to calculate the amount of sales of priority 1 customers in the USA in 1970 for my data set, for instance - but how can I link the sum if formula to this table to check if this customer name is priority 1?

Do I need to use a sum product?

Thank you very much

 

[Eric W]

Welcome to the forum.

Here's one possibility:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer	Year of sale	Country	Amount			Customer	Priority		Year	Country	Priority	Sum
2	A	1970	USA	1			A	1		1970	USA	1	8269
3	A	1971	USA	2			B	2
4	A	1972	USA	4			C	3
5	A	1970	USA	8			D	1
6	B	1970	USA	16			E	1
7	C	1970	Canada	32			F	2
8	D	1970	USA	64			G	3
9	D	1970	Canada	128			H	4
10	E	1970	Japan	256			I	1
11	F	1969	China	512
12	G	1970	USA	1024
13	H	1990	USA	4098
14	I	1970	USA	8196

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula M2 =SUMPRODUCT($D$2:$D$14,--($B$2:$B$14=J2),--($C$2:$C$14=K2),--(SUMIF($G$2:$G$10,$A$2:$A$14,$H$2:$H$10)=L2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>