Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
Hi
=SUMPRODUCT(--('Global Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY(),0),--('Global Schedule'!$J3:$J$10000<DATE(YEAR(TODAY(),MONTH(TOD AY()+1,1),'Global Schedule'!$N$2:$N$10000) =SUMPRODUCT(--('Global Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY()+1,0),--('Global Schedule'!$J3:$J$10000<DATE(YEAR(TODAY(),MONTH(TOD AY()+2,1),'Global Schedule'!$N$2:$N$10000) =SUMPRODUCT(--('Global Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY()+2,0),'Global Schedule'!$N$2:$N$10000) Adjust ranges. NB! SUMPRODUCT doesn't allow references to whole column. Refer to range with enough spare rows, or use dynamic named ranges instead! NB! All ranges used in sumproduct as source ranges MUST be of same dimension (include same number of rows)! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
Try it with SUMPRODUCT - the main caveat is that you cannot use full column
references (unless you have Excel 2007). Here's an example of the first one: =SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global Schedule'!N1:N1000) I've limited this to 1000 rows - adjust if you have more. Hope this helps. Pete "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
What does the "--" portion of the formula for? (Located after "=SUMPRODUCT("
and before "(MONTH(....") "Pete_UK" wrote: Try it with SUMPRODUCT - the main caveat is that you cannot use full column references (unless you have Excel 2007). Here's an example of the first one: =SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global Schedule'!N1:N1000) I've limited this to 1000 rows - adjust if you have more. Hope this helps. Pete "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
Hi
"RyanH" wrote in message ... What does the "--" portion of the formula for? (Located after "=SUMPRODUCT(" and before "(MONTH(....") Converts logical ecpression to its numeric value. When you are multiplying directly in formula like =SUMPRODUCT((Range1=Value)*(Range2=Value2)*Range3) , then Excel does the converting for you. When you try =SUMPRODUCT((Range1=Value),(Range2=Value2).Range3) instead, Excel tries to multiply logical TRU/FALSE with a number, and this does'nt work. Better will be =SUMPRODUCT((Range1=Value)*1,(Range2=Value2)*1.Ran ge3) , where multiplying with 1 converts all parts of formula to numbers, and =SUMPRODUCT(--(Range1=Value),--(Range2=Value2).Range3) does exactly same, but is considered as preferable beacuse it's faster compared to other syntaxes. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Pete_UK" wrote: Try it with SUMPRODUCT - the main caveat is that you cannot use full column references (unless you have Excel 2007). Here's an example of the first one: =SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global Schedule'!N1:N1000) I've limited this to 1000 rows - adjust if you have more. Hope this helps. Pete "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
Try a Google for the phrase "double unary minus".
-- David Biddulph "RyanH" wrote in message ... What does the "--" portion of the formula for? (Located after "=SUMPRODUCT(" and before "(MONTH(....") "Pete_UK" wrote: Try it with SUMPRODUCT - the main caveat is that you cannot use full column references (unless you have Excel 2007). Here's an example of the first one: =SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global Schedule'!N1:N1000) I've limited this to 1000 rows - adjust if you have more. Hope this helps. Pete "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function Question
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RyanH wrote: What does the "--" portion of the formula for? (Located after "=SUMPRODUCT(" and before "(MONTH(....") "Pete_UK" wrote: Try it with SUMPRODUCT - the main caveat is that you cannot use full column references (unless you have Excel 2007). Here's an example of the first one: =SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global Schedule'!N1:N1000) I've limited this to 1000 rows - adjust if you have more. Hope this helps. Pete "RyanH" wrote in message ... I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows are used as a header. In Col. J there are dates and in Col. N there are prices. At any given time Col. J cells may or may not have a date. I would like the formula placed in a worksheet labeled 'Summary'. Here is what I have, but it does not seem to work. 1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the current month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date within the next month. =SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date after the next month. =SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function question (sumif countif conditional) | Excel Worksheet Functions | |||
SUMIF question | Excel Discussion (Misc queries) | |||
SUMIF function question | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Countif/Sumif function question | Excel Worksheet Functions |