Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
Try...
=SUMIF(B3:B350,"="&K6,L3:L350)-SUMIF(B3:B350,""&K2,L3:L350) Hope this helps! In article , "bdddd" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
Try this alternative:
=SUMPRODUCT((B3:B350=K6)*(B3:B350<=TODAY()),L3:L3 50) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bdddd" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
This works, but I'm not sure I understand why.
"Max" wrote: Try this alternative: =SUMPRODUCT((B3:B350=K6)*(B3:B350<=TODAY()),L3:L3 50) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bdddd" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
hi for detailed explantion of SUMPRODUCT visit http://www.xldynamic.com/source/xld.SUMPRODUCT.html -via135 bdddd Wrote: This works, but I'm not sure I understand why. "Max" wrote: Try this alternative: =SUMPRODUCT((B3:B350=K6)*(B3:B350<=TODAY()),L3:L3 50) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bdddd" wrote in message ... 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? -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=512055 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
"bdddd" wrote
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
Thank you for your help
"Max" wrote: "bdddd" wrote 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a range in SUMIF?
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bdddd" wrote in message ... Thank you for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
SumIf in Visible Cell Range | Excel Worksheet Functions | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |