Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Will SUMPRODUCT work for this?
I'm currently using the following formula to calculate certain cells in the
range only if they match in two columns. It works fine for getting a grand total. =SUMPRODUCT(--Logbook!T3:T65536<""),--(Logbook!U3:U65536<""),Logbook!T3:T65536)) But I'd like to be able to break this down by month in the date column. Ex: A B C 3-Jan 15 30 5-Jan 8 8 3-Mar 13 19 9-Mar 5 5 7-Jul 10 10 2-Jul 6 4 The values in columns B & C must match in order to be accepted. I'm stuck on how to break it down by month only. Not sure if this is important or not but not every row has a date or figure. As always, help is greatly apprreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Will SUMPRODUCT work for this?
Do you mean
=SUMPRODUCT(--(MONTH(A1:A100=1),--(B1:B100=C1:C100),B1:B100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Aaron Saulisberry" wrote in message ... I'm currently using the following formula to calculate certain cells in the range only if they match in two columns. It works fine for getting a grand total. =SUMPRODUCT(--Logbook!T3:T65536<""),--(Logbook!U3:U65536<""),Logbook!T3:T6 5536)) But I'd like to be able to break this down by month in the date column. Ex: A B C 3-Jan 15 30 5-Jan 8 8 3-Mar 13 19 9-Mar 5 5 7-Jul 10 10 2-Jul 6 4 The values in columns B & C must match in order to be accepted. I'm stuck on how to break it down by month only. Not sure if this is important or not but not every row has a date or figure. As always, help is greatly apprreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Will SUMPRODUCT work for this?
Aaron, I had to do something similar to this recently. I got around this by
creating a table that have the 1st day of each month in column A and the last day of each month in column B. Then the SUMPRODUCT will work as you can check against column A dn B of your new table and then whatever else you need to. HTH. "Aaron Saulisberry" wrote: I'm currently using the following formula to calculate certain cells in the range only if they match in two columns. It works fine for getting a grand total. =SUMPRODUCT(--Logbook!T3:T65536<""),--(Logbook!U3:U65536<""),Logbook!T3:T65536)) But I'd like to be able to break this down by month in the date column. Ex: A B C 3-Jan 15 30 5-Jan 8 8 3-Mar 13 19 9-Mar 5 5 7-Jul 10 10 2-Jul 6 4 The values in columns B & C must match in order to be accepted. I'm stuck on how to break it down by month only. Not sure if this is important or not but not every row has a date or figure. As always, help is greatly apprreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Will SUMPRODUCT work for this?
Great! That works... but
When I enter the ranges for columns A,B,C I get an error because there are blank cells in the range. Basically I have a running list of entries for each row. I'd like to have it set up to compensate for data continuiosly being added. Ex: I currently have only 500 rows, I'm predicting that this sheet will grow into the thousands. Thanks for the quick response... bonus points!! "Bob Phillips" wrote: Do you mean =SUMPRODUCT(--(MONTH(A1:A100=1),--(B1:B100=C1:C100),B1:B100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Aaron Saulisberry" wrote in message ... I'm currently using the following formula to calculate certain cells in the range only if they match in two columns. It works fine for getting a grand total. =SUMPRODUCT(--Logbook!T3:T65536<""),--(Logbook!U3:U65536<""),Logbook!T3:T6 5536)) But I'd like to be able to break this down by month in the date column. Ex: A B C 3-Jan 15 30 5-Jan 8 8 3-Mar 13 19 9-Mar 5 5 7-Jul 10 10 2-Jul 6 4 The values in columns B & C must match in order to be accepted. I'm stuck on how to break it down by month only. Not sure if this is important or not but not every row has a date or figure. As always, help is greatly apprreciated! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Will SUMPRODUCT work for this?
Do you mean you get a wring count for January? If so use
=SUMPRODUCT(--(A1:A10000<""),--(MONTH(A1:A10000=1),--(B1:B100=C1:C10000),B1 :B10000) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Aaron Saulisberry" wrote in message ... Great! That works... but When I enter the ranges for columns A,B,C I get an error because there are blank cells in the range. Basically I have a running list of entries for each row. I'd like to have it set up to compensate for data continuiosly being added. Ex: I currently have only 500 rows, I'm predicting that this sheet will grow into the thousands. Thanks for the quick response... bonus points!! "Bob Phillips" wrote: Do you mean =SUMPRODUCT(--(MONTH(A1:A100=1),--(B1:B100=C1:C100),B1:B100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Aaron Saulisberry" wrote in message ... I'm currently using the following formula to calculate certain cells in the range only if they match in two columns. It works fine for getting a grand total. =SUMPRODUCT(--Logbook!T3:T65536<""),--(Logbook!U3:U65536<""),Logbook!T3:T6 5536)) But I'd like to be able to break this down by month in the date column. Ex: A B C 3-Jan 15 30 5-Jan 8 8 3-Mar 13 19 9-Mar 5 5 7-Jul 10 10 2-Jul 6 4 The values in columns B & C must match in order to be accepted. I'm stuck on how to break it down by month only. Not sure if this is important or not but not every row has a date or figure. As always, help is greatly apprreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DIV/0! error in SumProduct formula with no division | Excel Worksheet Functions | |||
Tab key don't work in unprotected cells in a protected sheet | Excel Discussion (Misc queries) | |||
Some Excel links don't work | Excel Discussion (Misc queries) | |||
creating an invoice for work | Excel Worksheet Functions | |||
Sumproduct HELP | Excel Worksheet Functions |