using sumproduct with multiple criteria

Glenn_Turner

New Member
Joined
Dec 1, 2016
Messages
3
Hi,

I have been trying to sumproduct cells with one row and two column search criteria. C3:K29 is a pivot table from a networked cube data source. It shows date data in A:C and location parameters in rows 7:10. This is a very small section of my original data set, which has several combinations of the column headers you see below plus others that i have excluded from this sample.

Column A is a date i created as the date in the pivot table is not recognised as a date by excel, even with formatting. I want this date to start with the start of the current month (=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1))

Column B combines the date from column A and adds it to the shift identifier in column C as i need to break information down to day and night shift (=A12&C12)

My first sum product works as I simplified one level of search parameters by combining SH01 & Crusher in row 1 of the sheet, but i would still like to also search for ROM and ROM-PR.

I want to search for a date in Column A, I then want to search the column headers to find othes, in this case SH01& stockpile & ROM & ROM-PR, then sum the values for those which are true


I can get a sum product formula to work if i only want to search the date and one of the identifiers for the top row, but when i add another for looking for both ROM and ROM-PR it gives me a #N/A value

My formulas area at the bottom, im sure it is something simple to do with trying to add additional search parameters. Thanks in advance.


Sheet2

*ABCDEFGHIJK
1***SH01CrusherSH01CrusherSH01CrusherSH01CrusherSH01DumpSH01DumpSH01StockpileSH01Stockpile
2***SH01SH01SH01SH01SH01SH01SH01SH01
3***CrusherCrusherCrusherCrusherDumpDumpStockpileStockpile
4***CR01 NTHCR01 NTHCR01 STHCR01 STH7WD340_NN7WD340_PAF7WD340_PAFGSS CRUSHER PAD
5*Row LabelsRow LabelsROMROM-PRROMROM-PRNDOLNDOLROM-PRROM
6426754267501-NOV-1601-NOV-16********
74267542675DD020860277400000
84267542675NN010430258630000
9426764267602-NOV-1602-NOV-16********
104267642676DD09386060490000
114267642676NN000187710000
12426774267703-NOV-1603-NOV-16********
134267742677DD045890137660000
144267742677NN00000000
15426784267804-NOV-1604-NOV-16********
164267842678DD00000000
174267842678NN00000000
18426794267905-NOV-1605-NOV-16********
194267942679DD01563187700010001640
204267942679NN209209938660490000
21426804268006-NOV-1606-NOV-16********
224268042680DD010430120970000
234268042680NN0019606139740000
24426814268107-NOV-1607-NOV-16********
25***********
26***********
27**05-Nov-16D42679D******
28***********
29*SH01CrusherSH01Crusher3440.13******
30*SH01StockpileSH01Stockpile#N/A******
31*ROM*********
32*ROM-PR*********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:110px;"><col style="width:161px;"><col style="width:117px;"><col style="width:139px;"><col style="width:117px;"><col style="width:139px;"><col style="width:110px;"><col style="width:110px;"><col style="width:118px;"><col style="width:131px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E29=SUMPRODUCT(($A$1:$K$1=$D$29)*($B$1:$B$24=$E$27),$A$1:$K$24)
E30=SUMPRODUCT(($A$1:$K$1=$D30)*($B$1:$B$24=$E$27)*(A5:K5=B31)*(Production!A37:JN37=B32),$A$1:$K$24)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Glenn_Turner

New Member
Joined
Dec 1, 2016
Messages
3
Sorry i realised i had an error in in the formula for cell E30

it should be =SUMPRODUCT(($A$1:$K$1=$D30)*($B$1:$B$24=$E$27)*(A5:K5=B31)*(A5:K5=B32),$A$1:$K$24)

it gives a zero value.

So it finds no 1 or true values and returns a 0 because it is trying to multiply by 0.
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

This part :-
Code:
(A5:K5=B31)*(A5:K5=B32)
is self-negating so you will always get zero.

Try (not tested) :-
Code:
=SUMPRODUCT(($A$1:$K$1=$D30)*($B$1:$B$24=$E$27)*((A5:K5=B31)+(A5:K5=B32)),$A$1:$K$24)

hth
 

Glenn_Turner

New Member
Joined
Dec 1, 2016
Messages
3
Hi ukmikeb

thanks that worked a treat. i knew it would be something small.

Really appreciate it

Glenn
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

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