Sumifs with list and criteria

Rmcano

New Member
Joined
Jan 8, 2017
Messages
1
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,645
Welcome to the forum.

Here's one possibility:

Excel 2012
ABCDEFGHIJKLM
1CustomerYear of saleCountryAmountCustomerPriorityYearCountryPrioritySum
2A1970USA1A11970USA18269
3A1971USA2B2
4A1972USA4C3
5A1970USA8D1
6B1970USA16E1
7C1970Canada32F2
8D1970USA64G3
9D1970Canada128H4
10E1970Japan256I1
11F1969China512
12G1970USA1024
13H1990USA4098
14I1970USA8196

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

Worksheet Formulas
CellFormula
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

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
Top