Sumif or Sumproduct with multiple criteria

[BPSJACK]

Hi,

I have a list of sales, the rep that made the sale, and from which division the sale came from.

I'm trying to create a table that will sum each rep by sales they have in each division:
(Totals in columns G>I)

C	D	E	F	G	H	I
SALES	REP	DIV		REP	A	B
100	14A	A		14A	100	900
900	14A	B		14B

I've played around with sum if,products and signs but I just can't get it to do what I want.
In column G I have a list of reps, and I want to sum the sales values by divisions in columns H & I

I've been trying =SUMPRODUCT(SIGN((D:D=G2)+(E:E=H1))*C:C) but it's not giving me the result I need.

Can anyone offer any suggestions?

Thanks.

[Tony Valko]

Try this...

Entered in H2:

=SUMIFS($C:$C,$D:$D,$G2,$E:$E,H$1)

Copy across to I2 then down as needed.

[canapone]

Hi,

sum per division:

=SUMIF($E$2:$E$1000,H$1,$C$2:$C$1000)

to be copied to the right

Hope that helps


Edit: sorry Tony Valko, I did not mean to overlap! Regards

[samba_ravi]

pls attach sample excel file

[BPSJACK]

Sample atatched

[Tony Valko]

The formula in reply #2 does what you want.

[BPSJACK]

It absolutely does!

Thanks for your help!

[Tony Valko]

You're welcome. Thanks for the feedback!