| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a Date in SumIf
I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100
contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Date in SumIf
try this one that I use
=SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD) -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100 contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Date in SumIf
I'm sorry, but I'm going to need a little more help. I don't understand.
FYI, column A is formatted mm/dd/yyyy, if that means anything. "Don Guillett" wrote in message ... try this one that I use =SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD) -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100 contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Date in SumIf
I'm having trouble applying this. I tried this and got an error that said
the formula contained an error: SUMIF((MONTH(A3:A100)=9),C3:C100) What is wrong here? S. Jackson "Don Guillett" wrote in message ... checksa is a defined name I gave to a range in columna. You may prefer to use a2:a200 d2:d200 instead change the 1 to 9 for sep -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I'm sorry, but I'm going to need a little more help. I don't understand. FYI, column A is formatted mm/dd/yyyy, if that means anything. "Don Guillett" wrote in message ... try this one that I use =SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD) -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100 contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Date in SumIf
I finally figured it out:
=SUMPRODUCT((MONTH(A3:A100)=9)*C3:C100) But, I was wondering if there was a way to use the SUMIF function. What is the proper syntax? Shelly "S Jackson" wrote in message ... I'm having trouble applying this. I tried this and got an error that said the formula contained an error: SUMIF((MONTH(A3:A100)=9),C3:C100) What is wrong here? S. Jackson "Don Guillett" wrote in message ... checksa is a defined name I gave to a range in columna. You may prefer to use a2:a200 d2:d200 instead change the 1 to 9 for sep -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I'm sorry, but I'm going to need a little more help. I don't understand. FYI, column A is formatted mm/dd/yyyy, if that means anything. "Don Guillett" wrote in message ... try this one that I use =SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD) -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100 contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Date in SumIf
=Sumif() wouldn't work on this.
You could insert another column that returns the month and use that: =SUMIF(B1:B100,9,C1:C100) or you could use an array formula: =SUM(IF(MONTH(A1:A100)=9,C1:C100)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) But =sumproduct() is easier to type and doesn't require the curly brackets. But be aware that if you're looking for January figures, that empty cells will look like they have months of 1 (January). You may want: =sumproduct(--(isnumber(a3:a100)),--(month(a3:a100)=1),c3:c100) S Jackson wrote: I finally figured it out: =SUMPRODUCT((MONTH(A3:A100)=9)*C3:C100) But, I was wondering if there was a way to use the SUMIF function. What is the proper syntax? Shelly "S Jackson" wrote in message ... I'm having trouble applying this. I tried this and got an error that said the formula contained an error: SUMIF((MONTH(A3:A100)=9),C3:C100) What is wrong here? S. Jackson "Don Guillett" wrote in message ... checksa is a defined name I gave to a range in columna. You may prefer to use a2:a200 d2:d200 instead change the 1 to 9 for sep -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I'm sorry, but I'm going to need a little more help. I don't understand. FYI, column A is formatted mm/dd/yyyy, if that means anything. "Don Guillett" wrote in message ... try this one that I use =SUMPRODUCT((MONTH(ChecksA)=1)*ChecksD) -- Don Guillett SalesAid Software "S Jackson" wrote in message ... I am very new to forumlas/excel. I want a cell to sum C3:C100, if A3:A100 contains an entry for September. cells A3:A100 are formatted as dates: 09/01/2005. I don't know how to fill in the blank in the following formula to tell the worksheet to only sum entries for the month of September: =SUMIF(A3:A100,_________,C3:C100) How do I refer to "90" in the criteria section of this formula? TIA S. Jackson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif referring to range names formulas not updating | Excel Worksheet Functions | |||
| SUMIF function referring to values on different Worksheet | Excel Worksheet Functions | |||
| Totalling by referring to 2 date ranges | Excel Discussion (Misc queries) | |||
| Totalling by referring to 2 date ranges | Excel Discussion (Misc queries) | |||
| Referring Date in VBA | Excel Programming | |||
Linear Mode
