Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
=sumproduct((month(a2:a22)=11)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Phendrena" wrote in message ... I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)
"Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust
it to my worsheet wich the only difference than Phendrena's is that mine has numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30 is November 2007. Thanks. -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
=SUMPRODUCT(--(MONTH(A1:A30)=11),--(YEAR(A1:A30)=2007),B1:B30)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Learning Excel" wrote in message ... Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust it to my worsheet wich the only difference than Phendrena's is that mine has numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30 is November 2007. Thanks. -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
For some reason, is not working.
To clarify my question: In B1 to B30 I have values 2,3,5,9,80,95... that I' like the total on the month of November ( in column A). Thanks -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Bob Phillips" wrote: =SUMPRODUCT(--(MONTH(A1:A30)=11),--(YEAR(A1:A30)=2007),B1:B30) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Learning Excel" wrote in message ... Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust it to my worsheet wich the only difference than Phendrena's is that mine has numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30 is November 2007. Thanks. -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
Glad you got it.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Learning Excel" wrote in message ... YES SIR! It worked. My bad. Good work. -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Don Guillett" wrote: Try the formulas presented. -- Don Guillett Microsoft MVP Excel SalesAid Software "Learning Excel" wrote in message ... Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust it to my worsheet wich the only difference than Phendrena's is that mine has numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30 is November 2007. Thanks. -- Socrates said: I only know, I don''''t know nothing. I say : I don''''t even know, I don''''t know nothing. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
thanks for the replies,
So far i'm not having much luck, i have to say i am a novice when it comes to most things on excel, so i thought i'd help if i gave you the formula to see if you could suggest where i am going wrong: =SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502) Range: '[name.xls]Mid-Term'!$B$3:$B$502 Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503) Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502 Any further help would be most welcome. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
Let me try and show you how the spreadsheet is setup:
Source Data: Col B ..... ..... Col G Date ...... .... Reason Date ...... .... Reason Date : is formatted as dd/mm/yyyy and the user presses the keyboard shortcut (CTRL+;) to add in the date. Reason : is just various text reasons that are picked from a drop-down menu, all i need to do is just add up if the date if within a specific month regardless of what text is there as long as there is text. Summary Data: Col B ..... ..... ...... Col D Name 1 .... .... ... .... Data Name 2 .... .... ... .... Data Name 3 .... .... ... .... Data So it's the Col D data field where i am going to pulling the information to from the source (seperate workbooks). Hope this helps with my query!!! thanks, |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF (Excel '97)
Look at what you've asked your formula to do.
You have asked it to add the values in column G on your Mid-Term sheet in name.xls, for the rows where the value in column B on that sheet is equal to the value of the specfied criterion, which is obtained from by multiplying column B on your current sheet by two Booleans, one checking that the month of the date in column B of your current sheet is November, and the other checking that the year in that column is 2007. [And additionally you've got unequal ranges, some going to row 502 and some to 503.] I doubt whether that's what you intended. I guess that the SUMPRODUCT formula was intended to give your answer, not to be used as the crierion in the SUMIF formula. -- David Biddulph "Phendrena" wrote in message ... thanks for the replies, So far i'm not having much luck, i have to say i am a novice when it comes to most things on excel, so i thought i'd help if i gave you the formula to see if you could suggest where i am going wrong: =SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502) Range: '[name.xls]Mid-Term'!$B$3:$B$502 Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503) Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502 Any further help would be most welcome. "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100) "Phendrena" wrote: I want to sum a particular range using a date range as a criteria, I have the dates in the worksheet formatted as dd/mm/yyyy. How do i format this in the criteria? For example dates for November would only be included anything else would be ignored. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bug in help for Excel SUMIF function | Excel Worksheet Functions | |||
Excel sumif functions? | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
Formula in Excel--SUMIF | Setting up and Configuration of Excel | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |