| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
--(MONTH(Cancellation!B16:B1013)=1)
Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
try =A1
if it does not work try =A1*1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
for converting May to try using CHOOSE function
=CHOOSE(A1,"January","February",.................. ....................,"December") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
Assuming you have a real date in A1 just formatted as the month name, use
--(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
Many thanks Bob, the second one worked as the maonth is entered as normal text.
"Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
Another quick question if i might....
As the validation list is using the months, if i wanted the list to have the option for Year as well as months.... so the user choose Yearly Total instead of choosing a month, how would i get that to work? Thanks, "Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
This should do what you want
=--(IF(ISNUMBER(A1),YEAR(Cancellation!B16:B23)=(A1),M ONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"&A1 )))) -- __________________________________ HTH Bob "Phendrena" wrote in message ... Another quick question if i might.... As the validation list is using the months, if i wanted the list to have the option for Year as well as months.... so the user choose Yearly Total instead of choosing a month, how would i get that to work? Thanks, "Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
Hi Bob,
Thanks for the reply. Unfortunetly I can't seem to get it to work. This is the full formula that i'm using : =SUMPRODUCT(--(Cancellation!E16:E1013=C5),--(Cancellation!F16:F1013=C6),--(IF(ISNUMBER(C7),YEAR(Cancellation!B16:B1023)=(C7) ,MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&C7)))) As you can see this is just one part of a larger array. I'm just getting #VALUE! regardless of the option picked from the validation drop-down (cell C7) "Bob Phillips" wrote: This should do what you want =--(IF(ISNUMBER(A1),YEAR(Cancellation!B16:B23)=(A1),M ONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"&A1 )))) -- __________________________________ HTH Bob "Phendrena" wrote in message ... Another quick question if i might.... As the validation list is using the months, if i wanted the list to have the option for Year as well as months.... so the user choose Yearly Total instead of choosing a month, how would i get that to work? Thanks, "Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
I can't say I like it, but this works
=IF(ISNUMBER(C7),SUMPRODUCT(--(Cancellation!E16:E23=C5),--(Cancellation!F16:F23=C6),--(YEAR(Cancellation!B16:B23)=C7)), SUMPRODUCT(--(Cancellation!E16:E23=C5),--(Cancellation!F16:F23=C6),--(MONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"& C7))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... Hi Bob, Thanks for the reply. Unfortunetly I can't seem to get it to work. This is the full formula that i'm using : =SUMPRODUCT(--(Cancellation!E16:E1013=C5),--(Cancellation!F16:F1013=C6),--(IF(ISNUMBER(C7),YEAR(Cancellation!B16:B1023)=(C7) ,MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&C7)))) As you can see this is just one part of a larger array. I'm just getting #VALUE! regardless of the option picked from the validation drop-down (cell C7) "Bob Phillips" wrote: This should do what you want =--(IF(ISNUMBER(A1),YEAR(Cancellation!B16:B23)=(A1),M ONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"&A1 )))) -- __________________________________ HTH Bob "Phendrena" wrote in message ... Another quick question if i might.... As the validation list is using the months, if i wanted the list to have the option for Year as well as months.... so the user choose Yearly Total instead of choosing a month, how would i get that to work? Thanks, "Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT - Using Month
Got it! I was a bit slow there.
You have to array-enter it, so you might as well just use SUM =SUM((Cancellation!E16:E1013=C5)*(Cancellation!F16 :F1013=C6)* (IF(ISNUMBER(C7),(YEAR(Cancellation!B16:B1013)=C7) ,MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&C7))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... Hi Bob, Thanks for the reply. Unfortunetly I can't seem to get it to work. This is the full formula that i'm using : =SUMPRODUCT(--(Cancellation!E16:E1013=C5),--(Cancellation!F16:F1013=C6),--(IF(ISNUMBER(C7),YEAR(Cancellation!B16:B1023)=(C7) ,MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&C7)))) As you can see this is just one part of a larger array. I'm just getting #VALUE! regardless of the option picked from the validation drop-down (cell C7) "Bob Phillips" wrote: This should do what you want =--(IF(ISNUMBER(A1),YEAR(Cancellation!B16:B23)=(A1),M ONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"&A1 )))) -- __________________________________ HTH Bob "Phendrena" wrote in message ... Another quick question if i might.... As the validation list is using the months, if i wanted the list to have the option for Year as well as months.... so the user choose Yearly Total instead of choosing a month, how would i get that to work? Thanks, "Bob Phillips" wrote: Assuming you have a real date in A1 just formatted as the month name, use --(MONTH(Cancellation!B16:B1013)=MONTH(A1)) if it is a month name, then use =--(MONTH(Cancellation!B16:B1013)=MONTH(DATEVALUE("01 "&A1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... --(MONTH(Cancellation!B16:B1013)=1) Using the above with the SUMPRODUCT function would show results from January. Now, I have a drop-down list that shows the months as text in Cell A1. How can i adjust the above to use =A1 instead of =1? Or what i need setup a seperate cell reference to use the number? So if Cell A1=May then cell A2=5? If so could you suggest a quick formula/function to convert May to 5 etc Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Month Conversion in SUMPRODUCT | Excel Worksheet Functions | |||
| SUMPRODUCT - Count Previous Month | Excel Worksheet Functions | |||
| sumproduct for month and year | Excel Discussion (Misc queries) | |||
| Problem with sumproduct and month=1 | Excel Worksheet Functions | |||
| sumproduct to add total amounts for the month | Excel Worksheet Functions | |||
Hybrid Mode
