using sumproduct with multiple criteria

[Glenn_Turner]

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

*	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	SH01Crusher	SH01Crusher	SH01Crusher	SH01Crusher	SH01Dump	SH01Dump	SH01Stockpile	SH01Stockpile
2	*	*	*	SH01	SH01	SH01	SH01	SH01	SH01	SH01	SH01
3	*	*	*	Crusher	Crusher	Crusher	Crusher	Dump	Dump	Stockpile	Stockpile
4	*	*	*	CR01 NTH	CR01 NTH	CR01 STH	CR01 STH	7WD340_NN	7WD340_PAF	7WD340_PAF	GSS CRUSHER PAD
5	*	Row Labels	Row Labels	ROM	ROM-PR	ROM	ROM-PR	NDOL	NDOL	ROM-PR	ROM
6	42675	4267501-NOV-16	01-NOV-16	*	*	*	*	*	*	*	*
7	42675	42675D	D	0	2086	0	27740	0	0	0	0
8	42675	42675N	N	0	1043	0	25863	0	0	0	0
9	42676	4267602-NOV-16	02-NOV-16	*	*	*	*	*	*	*	*
10	42676	42676D	D	0	9386	0	6049	0	0	0	0
11	42676	42676N	N	0	0	0	18771	0	0	0	0
12	42677	4267703-NOV-16	03-NOV-16	*	*	*	*	*	*	*	*
13	42677	42677D	D	0	4589	0	13766	0	0	0	0
14	42677	42677N	N	0	0	0	0	0	0	0	0
15	42678	4267804-NOV-16	04-NOV-16	*	*	*	*	*	*	*	*
16	42678	42678D	D	0	0	0	0	0	0	0	0
17	42678	42678N	N	0	0	0	0	0	0	0	0
18	42679	4267905-NOV-16	05-NOV-16	*	*	*	*	*	*	*	*
19	42679	42679D	D	0	1563	1877	0	0	0	1000	1640
20	42679	42679N	N	209	209	9386	6049	0	0	0	0
21	42680	4268006-NOV-16	06-NOV-16	*	*	*	*	*	*	*	*
22	42680	42680D	D	0	1043	0	12097	0	0	0	0
23	42680	42680N	N	0	0	19606	13974	0	0	0	0
24	42681	4268107-NOV-16	07-NOV-16	*	*	*	*	*	*	*	*
25	*	*	*	*	*	*	*	*	*	*	*
26	*	*	*	*	*	*	*	*	*	*	*
27	*	*	05-Nov-16	D	42679D	*	*	*	*	*	*
28	*	*	*	*	*	*	*	*	*	*	*
29	*	SH01	Crusher	SH01Crusher	3440.13	*	*	*	*	*	*
30	*	SH01	Stockpile	SH01Stockpile	#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

Cell Formula 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

 

[Glenn_Turner]

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]

Hi

This part :-

(A5:K5=B31)*(A5:K5=B32)

is self-negating so you will always get zero.

Try (not tested) :-

=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]

Hi ukmikeb

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

Really appreciate it

Glenn

 

[ukmikeb]

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

Really appreciate it

You're welcome.

Thanks for the feedback.

Come back to MrExcel when you have further questions.