software

I am trying to SUM by month, what is spent on different products or buckets.
The formula I have been trying to make work is:
'=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) whe

RANGE=D10:E40. Months are in Col D1040, and buckets are in E10:E40.
CRITERIA=(AND(MONTH(1),G3))
SUMRANGE=F10:F40
I have tried several others but don't seem to get even close.
Thanks
Dave

=SUMPRODUCT(--(ISNUMBER(D1040),--(MONTH(D1040)=1),--(E10:E40=G3),F10:F40
)

--

Regards,

Peo Sjoblom

quot;Xray_Davequot; gt; wrote in message
...
gt; I am trying to SUM by month, what is spent on different products or
buckets.
gt; The formula I have been trying to make work is:
gt; '=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) whe
gt;
gt; RANGE=D10:E40. Months are in Col D1040, and buckets are in E10:E40.
gt; CRITERIA=(AND(MONTH(1),G3))
gt; SUMRANGE=F10:F40
gt; I have tried several others but don't seem to get even close.
gt; Thanks
gt; Dave
Take a look at these:
www.cpearson.com/excel/array.htm
www.contextures.com/xlFunctio...tml#SumProduct

quot;Xray_Davequot; wrote:

gt; I am trying to SUM by month, what is spent on different products or buckets.
gt; The formula I have been trying to make work is:
gt; '=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) whe
gt;
gt; RANGE=D10:E40. Months are in Col D1040, and buckets are in E10:E40.
gt; CRITERIA=(AND(MONTH(1),G3))
gt; SUMRANGE=F10:F40
gt; I have tried several others but don't seem to get even close.
gt; Thanks
gt; Dave

Scott Wagner,
Thanks for the references, they were right-on target.
Xray_Dave

quot;Scott Wagnerquot; wrote:

gt; Take a look at these:
gt; www.cpearson.com/excel/array.htm
gt; www.contextures.com/xlFunctio...tml#SumProduct
gt;
gt; quot;Xray_Davequot; wrote:
gt;
gt; gt; I am trying to SUM by month, what is spent on different products or buckets.
gt; gt; The formula I have been trying to make work is:
gt; gt; '=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) whe
gt; gt;
gt; gt; RANGE=D10:E40. Months are in Col D1040, and buckets are in E10:E40.
gt; gt; CRITERIA=(AND(MONTH(1),G3))
gt; gt; SUMRANGE=F10:F40
gt; gt; I have tried several others but don't seem to get even close.
gt; gt; Thanks
gt; gt; Dave

Peo Sjoblom,
Thanks for your reply, they were of great help.
Xray_Dave

quot;Peo Sjoblomquot; wrote:

gt; =SUMPRODUCT(--(ISNUMBER(D1040),--(MONTH(D1040)=1),--(E10:E40=G3),F10:F40
gt; )
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;Xray_Davequot; gt; wrote in message
gt; ...
gt; gt; I am trying to SUM by month, what is spent on different products or
gt; buckets.
gt; gt; The formula I have been trying to make work is:
gt; gt; '=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) whe
gt; gt;
gt; gt; RANGE=D10:E40. Months are in Col D1040, and buckets are in E10:E40.
gt; gt; CRITERIA=(AND(MONTH(1),G3))
gt; gt; SUMRANGE=F10:F40
gt; gt; I have tried several others but don't seem to get even close.
gt; gt; Thanks
gt; gt; Dave
gt;
gt;
gt;


I've also been playing with this, trying to use either IF, or SUMIF, or
SUMPRODUCT, trying to make this work (adding B C, compare with D, and
determine if the sum is =, lt;, or gt;, than D, and giving the result in
E):

If B9 C9=D9, then 0
but if B9 C9gt;D9, then B9 C9-D9 (to show that quot;Dquot; is x-amount less than
B C)
but if B9 C9lt;D9, then D9-B9 C9 (to show that quot;Dquot; is x-amount more than
B C)

Am I making sense?
LOL - I'm sure I'm close, but I keep getting the quot;too many argumentsquot;
error

THANK YOU --
navychef
------------------------------------------------------------------------
navychef's Profile: www.excelforum.com/member.php...oamp;userid=29457
View this thread: www.excelforum.com/showthread...hreadid=493042