sumifs help needed

[smgomez]

I have the following formula that is not working. Here is what I want the formula to do in cell B2 of the total worksheet: sum columns H-K from the sub worksheet where the sum of columns F and G <0 where the value in sub worksheet column A matches the value in column A in the total worksheet. The values in column A on both worksheets is an employee number and there are many instances of the same value

=SUMIFS(Sub!H$2:Sub!K$2,Sub!A$2:Sub!A$904,A2,Sub!F$2:Sub!G$2,"<0")

SUB WORKSHEET

TOTAL WORKSHEET

A	B
321654

 

[Eric W]

Try:

=SUMPRODUCT(Sub!$H$2:$K$904*(Sub!$A$2:$A$904=A1)*(Sub!$F$2:$F$904+Sub!$G$2:$G$904<0))

Adjusting the ranges as necessary.

 

[smgomez]

Thank you for the formula, I think we are close. The value I get with this formula is zero, I should get $710.02. I also changed =A1 to =A2

 

[Eric W]

With the Sub sheet like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	321654					0	0	710.02	0	0	0
3	321654					1	2		99	22	1
Sub

I get this on the Total sheet:

Book1
	A	B
1
2	321654	0
Total

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(Sub!$H$2:$K$20*(Sub!$A$2:$A$20=A2)*(Sub!$F$2:$F$20+Sub!$G$2:$G$20<0))

The way you explained it, and with your example, I'd expect a value of zero. Do you mean that you want to sum the values if the sum of the F:G columns is less than OR EQUAL to 0? If so, change the formula to:

=SUMPRODUCT(Sub!$H$2:$K$20*(Sub!$A$2:$A$20=A2)*(Sub!$F$2:$F$20+Sub!$G$2:$G$20<=0))

 

[smgomez]

=SUMPRODUCT(Sub!$H$2:$K$20*(Sub!$A$2:$A$20=A2)*(Sub!$F$2:$F$20+Sub!$G$2:$G$20<=0))

OMG, I really missed that. Works perfectly. Thank you!

 

[Eric W]

Happy to help! Thanks for the feedback.