Summing up based on a rather awkward critera

[Pauljj]

I need to be able to present 2 figures, 1 is the stat and 1 is the number of bookings, based on an employee. The user can select an employee from a drop down on another sheet, then they should be able to see how much stat in a month and how many bookings in a month

So If the user selects employee3 in the March field it should say 348.82 and for bookings it should say 9

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
5			834.4637			762.5961			778.9916			866.5657			921.7855			791.425
6			Clients	Stat	Bookings	Clients	Stat	Bookings	Clients	Stat	Bookings	Clients	Stat	Bookings	Clients	Stat	Bookings	Clients
7			1			2			3			4			5			6
8			40607			40614			40621			40628			40635			40642
9	Employee1		2.816901	41.29577	1.408451	5.633803	88.23944	2.816901	8.450704	169.5211	4.225352	0	0	0	1.408451	18.56338	1.408451	5.633803
10	Employee2		0	0	0	6	63.05	3	4	83.14	2	6	157.52	4	13	163.88	6	7
11	Employee3		4	62.48	3	8	153.53	4	1	23.23	1	2	109.58	1	3	58.48	2	4
12	Employee4
13	Employee5
14	Employee6
15	Employee7
16	Employee8
17			March			March			March			March			April			April
Sheet1

I have tried using various different SUMPRODUCT functions but to no avail. Can anyone help on this one please ?

 

[jasonb75]

Volatile but functional, expand the ranges to suit and point the descriptive text to the cells with the dropdowns.

=SUMIF(INDEX(OFFSET($C$6,,MATCH("March",$C$17:$R$17,0)-1,ROWS($C$6:$C$17),COUNTIF($C$17:$R$17,"March")*3),1,0),
"Stat",INDEX(OFFSET($C$6,,MATCH("March",$C$17:$R$17,0)-1,ROWS($C$6:$C$17),COUNTIF($C$17:$R$17,"March")*3),MATCH("Employee3",$A$6:$A$16,0),0))

 

[jasonb75]

Forget that suggestion, can be done easily if you tidy up your source data.

First fill in the blanks in row 17, you should have 12 cells with "March", not 4.

Then =SUMPRODUCT(($A$9:$A$16="Employee3")*($C$6:$R$6="Stat")*($C$17:$R$17="March")*C9:R16)

Should suffice.

Didn't work first time i tried it, but realised i had paranthesis wrong.

 

[barry houdini]

You could use SUMPRODUCT for "Stat" like this

=SUMPRODUCT((C6:Z6="Stat")*(B17:Y17="March")*(A9:A16="employee3"),C9:Z16)

for Bookings try

=SUMPRODUCT((C6:Z6="Bookings")*(A17:X17="March")*(A9:A16="employee3"),C9:Z16)

Note that I used a different range for the month in the second one.......

 

[Pauljj]

Thank you both very much, you have saved me from throwing my screen out the window. I have up till now used the following way when writing the SUMPRODUCT formula

SUMPRODUCT(--($A$9:$A$16="Employee3"),--( etc etc

This was giving me a #VALUE error, which is why I ditched the idea of using the function. Would you be so kind as yo explain the difference between what I was using and how you've solved my problem

Thanking you very much in advance

 

[barry houdini]

Remember that in SUMPRODUCT all the ranges need to be the same size and shape.

In a "normal" use of SUMPRODUCT you may have several columns of the same size with different criteria. For that you can use either syntax......but here you have some criteria in rows and some in columns, so multiplying those gives you a "matrix" of the correct size, e.g. in this formula

=SUMPRODUCT((C6:Z6="Stat")*(B17:Y17="March")*(A9:A16="employee3"),C9:Z16)

the red part results in a matrix of 1 and 0 values that is 8 rows long by 24 columns wide, i.e. the same size as C9:Z16

 

[Pauljj]

Thank you Barry, much appreciated