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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,326
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,018
Messages
5,856,840
Members
431,836
Latest member
jj0911

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