How to use a range in SUMIF?

[Guest]

I'm trying to find a month-to-date total for each of several years. I used
the following formula:
=SUMIF(B3:B350,AND(">="&K6,"<="&K2),L3:L350)
where B3:B350 are dates
K6 is the first of the month
K2 is Today
L3:L350 contain the amounts to total
The criteria give a "#Value!" error.

Any suggestions?

 

[Domenic]

Try...

=SUMIF(B3:B350,">="&K6,L3:L350)-SUMIF(B3:B350,">"&K2,L3:L350)

Hope this helps!

 

[Max]

Try this alternative:

=SUMPRODUCT((B3:B350>=K6)*(B3:B350<=TODAY()),L3:L350)

 

[Guest]

This works, but I'm not sure I understand why.

Try this alternative:

=SUMPRODUCT((B3:B350>=K6)*(B3:B350<=TODAY()),L3:L350)

 

[via135]

hi

for detailed explantion of SUMPRODUCT visit

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

-via135

 

[Max]

This works, but I'm not sure I understand why.

Try Bob Phillips' excellent coverage on SUMPRODUCT at his:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Scroll down to "SUMPRODUCT Explained" where you'll find all the details
fully explained. Take the time to look through the entire white paper, you
will be well on your way to mastering it very quickly ..

A short take on it ..

=SUMPRODUCT((B3:B350>=K6)*

(B3:B350<=TODAY()),L3:L350)

The 2 condition tests:
B3:B350>=K6
B3:B350<=TODAY()

will evaluate into 2 arrays of TRUEs / FALSEs:
{TRUE,FALSE,TRUE, ...}
{FALSE,FALSE,TRUE, ...}

depending on whether:
B3 >=K6, B4 >=K6, ...
B3 <=TODAY(), B4 <=TODAY(), ...

Multiplying the 2 TRUE / FALSE arrays together would then evaluate to a
single numeric array of 1's and 0's:
{0,0,1, ...}

Corresponding items from array 1 are multiplied with those from array 2,
viz. for the sample above:

TRUE x FALSE = 0
FALSE x FALSE = 0
TRUE x TRUE = 1

and then .. SUMPRODUCT({0,0,1, ...},L3:L350)
would evaluate each as:
(0 x L3) + (0 x L4) + (1 x L5) + ...
giving the final required sum from within the range L3:L350


---

 

[Guest]

Thank you for your help

Try Bob Phillips' excellent coverage on SUMPRODUCT at his:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Scroll down to "SUMPRODUCT Explained" where you'll find all the details
fully explained. Take the time to look through the entire white paper, you
will be well on your way to mastering it very quickly ..

A short take on it ..

(B3:B350<=TODAY()),L3:L350)

The 2 condition tests:
B3:B350>=K6
B3:B350<=TODAY()

will evaluate into 2 arrays of TRUEs / FALSEs:
{TRUE,FALSE,TRUE, ...}
{FALSE,FALSE,TRUE, ...}

depending on whether:
B3 >=K6, B4 >=K6, ...
B3 <=TODAY(), B4 <=TODAY(), ...

Multiplying the 2 TRUE / FALSE arrays together would then evaluate to a
single numeric array of 1's and 0's:
{0,0,1, ...}

Corresponding items from array 1 are multiplied with those from array 2,
viz. for the sample above:

TRUE x FALSE = 0
FALSE x FALSE = 0
TRUE x TRUE = 1

and then .. SUMPRODUCT({0,0,1, ...},L3:L350)
would evaluate each as:
(0 x L3) + (0 x L4) + (1 x L5) + ...
giving the final required sum from within the range L3:L350

 

[Max]

You're welcome !