Combine SUMIFS + SUMPRODUCT

[S_W_Langdon]

Hello Everyone,


I have been working on a formula for the past hour or two and am running into a brick wall and am hoping that you will be able to help by saying "Yes that is simple, this is what you do" or "Stop being stupid Excel can't do that!!!" lol


Anyway the problem I am having is around the SUMPRODUCT formula,
What I am trying to do is a SUMPRODUCT based on 2 Specific Criteria (hence why I thought SUMIFS might be the answer)


So what I am trying to do is something like this
SUMPRODUCT D:D & E:E / IF A= Employee 1 / AND C = 01/03/2018

This = 146 when done manually

A	B	C	D	E
Agent Name	Process	Date	Volume	AHT
Employee 1	Process 1	01/03/2018	1	4
Employee 1	Process 2	01/03/2018	0	128
Employee 1	Process 3	01/03/2018	0	196
Employee 1	Process 4	01/03/2018	2	47
Employee 1	Process 5	01/03/2018	0	91
Employee 1	Process 6	01/03/2018	1	49
Employee 1	Process 7	01/03/2018	0	90
Employee 2	Process 1	01/03/2018	0	4
Employee 3	Process 1	01/03/2018	2	4

<tbody>
</tbody>

I am trying to get this to then populate in a monthly grid

A	B	C	D	E	F	G
	01/03/18	02/03/18	03/03/18	04/03/18	05/03/18	06/03/18
Employee 1
Employee 2
Employee 3

<tbody>
</tbody>

Anyway, any and all help you can give is great
Thanks Everyone

(I hope the above is clear enough, am still new to the boards and trying to figure out everything )

 

[steve the fish]

Lets say your two tables are pasted into A1 of two sheets. Something like:

=SUMPRODUCT(--(Sheet2!$A$2:$A$1000=$A2),--(Sheet2!$C$2:$C$1000=B$1),Sheet2!$D$2:$D$1000,Sheet2!$E$2:$E$1000)

 

[S_W_Langdon]

Thanks you for the help

I forgot to add the '--' into my formula as this isn't something I have had to use before so was trying to calculate without it, and getting the error due to this.

Do you have a link that explains the use of the '--' as I would love to learn more about its use, rather than just adding it in and saying as if by Magic it now works... lol

I think this is what I love about Excel, no matter how much you learn, there always seems to be something else

Thanks again

 

[steve the fish]

Its just used to coerce a TRUE/FALSE result into a number. To excel TRUE is 1 and FALSE is 0 but when used in a sumproduct like this you need to coerce them. Try typing =TRUE into a cell and then try typing =--TRUE in to a cell. Using a double negative on a number changes nothing when applied to that numbers value eg --1 = 1. You will see some people use 0+ rather than -- which works in much the same way.