Nested SUMIFS with named range > named range is it possible?

[amanda92385]

Hi -

I have thousands of rows of data and I want to get the difference between the GTE Amount and the LY Award amount, but ONLY where the GTE Amount is greater than the LY Award. I'm using all named ranges. I get a value returned but it's not correct. Can I not do this with named ranges for the greater than portion of the formula? THANK YOU!!!!

SUMIFS(GTE_Amt,GTE,"Soft",GTE_Amt,">"&LY_Award) - SUMIFS(LY_Award,GTE,"Soft",GTE_Amt,">"&LY_Award)


So it would end up with 165,000 (which is the GTE amounts for Jim, Sue, Betty and Joan) - 60k (sum of LY Award for same ppl) = 105k.

[TABLE="width: 257"]
<TBODY>[TR]
[TD]Name</SPAN>[/TD]
[TD]GTE</SPAN>[/TD]
[TD]GTE Amt </SPAN>[/TD]
[TD]LY Award</SPAN>[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>[/TD]
[TD]Soft</SPAN>[/TD]
[TD]50000</SPAN>[/TD]
[TD]20000</SPAN>[/TD]
[/TR]
[TR]
[TD]Joe</SPAN>[/TD]
[TD]Soft</SPAN>[/TD]
[TD]10000</SPAN>[/TD]
[TD]15000</SPAN>[/TD]
[/TR]
[TR]
[TD]John</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue</SPAN>[/TD]
[TD]Soft</SPAN>[/TD]
[TD]30000</SPAN>[/TD]
[TD]10000</SPAN>[/TD]
[/TR]
[TR]
[TD]Betty</SPAN>[/TD]
[TD]Soft</SPAN>[/TD]
[TD]45000</SPAN>[/TD]
[TD]15000</SPAN>[/TD]
[/TR]
[TR]
[TD]Joan</SPAN>[/TD]
[TD]Soft</SPAN>[/TD]
[TD]40000</SPAN>[/TD]
[TD]15000</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Eric W]

Welcome to the forum!

Try:

=SUMPRODUCT((GTE_AMT-LY_Award),--(GTE_AMT>LY_Award),--(GTE="Soft"))

 

[jtakw]

Hi,

I'm getting the correct results as you described, what result are you getting?

Excel 2010
	A	B	C	D	E	F
1	Name	GTE	GTE Amt	LY Award
2	Jim	Soft	50000	20000		105000
3	Joe	Soft	10000	15000
4	John
5	Sally
6	Sue	Soft	30000	10000
7	Betty	Soft	45000	15000
8	Joan	Soft	40000	15000
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIFS(GTE_Amt,GTE,"Soft",GTE_Amt,">"&LY_Award)-SUMIFS(LY_Award,GTE,"Soft",GTE_Amt,">"&LY_Award)

Named Ranges
Name	Refers To	Cells
GTE	=Sheet1!$B$2:$B$8
GTE_Amt	=Sheet1!$C$2:$C$8
LY_Award	=Sheet1!$D$2:$D$8

 

[amanda92385]

AMAZING! Thank you so much!!! This worked perfectly. Try as I might I just cannot seem to get the hang of sumproduct - I have a serious mental block with it. Thank you so much for your help!

 

[amanda92385]

Hi,

I'm getting the correct results as you described, what result are you getting?

Excel 2010

	A	B	C	D	E	F
Name	GTE	GTE Amt	LY Award
Jim	Soft
Joe	Soft
John
Sally
Sue	Soft
Betty	Soft
Joan	Soft

<COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]105000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]30000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]45000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]40000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]F2[/TH]
[TD="align: left"]=SUMIFS(GTE_Amt,GTE,"Soft",GTE_Amt,">"&LY_Award)-SUMIFS(LY_Award,GTE,"Soft",GTE_Amt,">"&LY_Award)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]GTE[/TH]
[TD="align: left"]=Sheet1!$B$2:$B$8[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]GTE_Amt[/TH]
[TD="align: left"]=Sheet1!$C$2:$C$8[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]LY_Award[/TH]
[TD="align: left"]=Sheet1!$D$2:$D$8[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

It was really strange, it would miss like 2 or 3 people. I had a few additional criteria in my actual file (manager = X, plan = X etc.). I couldn't figure out why it would be fine for the first 10 ppl and then 3 random ppl would be missing. Eric's sumproduct formula seems to work, thankfully!

 

[jtakw]

It was really strange, it would miss like 2 or 3 people. I had a few additional criteria in my actual file (manager = X, plan = X etc.). I couldn't figure out why it would be fine for the first 10 ppl and then 3 random ppl would be missing. Eric's sumproduct formula seems to work, thankfully!

Glad you got a working solution. Welcome to the forum.

 

[Eric W]

The SUMPRODUCT still amazes me sometimes. If you look at the help screens for it, it looks like an ordinary, and somewhat unuseful function. But when you see some of the things it can do, it's amazing.

Glad we could help!