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, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
Thanks Bob,
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))))) This doesn't :- =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))))) Now to adjust it for all the other cells! cheers, "Bob Phillips" wrote: 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, |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
Actually, i'm lying... it does work assuming you use January, Februry etc, it
doesn't like anything else, so having the option Yearly Total" in the drop-down doesn't work for either formula "Phendrena" wrote: Thanks Bob, 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))))) This doesn't :- =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))))) Now to adjust it for all the other cells! cheers, "Bob Phillips" wrote: 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, |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
Did you array-enter it?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Phendrena" wrote in message ... Actually, i'm lying... it does work assuming you use January, Februry etc, it doesn't like anything else, so having the option Yearly Total" in the drop-down doesn't work for either formula "Phendrena" wrote: Thanks Bob, 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))))) This doesn't :- =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))))) Now to adjust it for all the other cells! cheers, "Bob Phillips" wrote: 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, |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
i'd have to say no to that one having never array-entered anything before.
I'll not be able to look at the sheet for a few days now. i'll lookup array-entering and let you know how I get on. this worksheet is getting rather complex! thanks for the help so far Bob "Bob Phillips" wrote: Did you array-enter it? -- HTH Bob |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
To array enter a formula, edit it, F2, and then instead of hitting Enter,
hit the Ctrl-Shift-Enter keys all together. Excel will enclose the formula in curly brackets {...}. If you ever re-edit it, you need to array-enter it again. -- __________________________________ HTH Bob "Phendrena" wrote in message ... i'd have to say no to that one having never array-entered anything before. I'll not be able to look at the sheet for a few days now. i'll lookup array-entering and let you know how I get on. this worksheet is getting rather complex! thanks for the help so far Bob "Bob Phillips" wrote: Did you array-enter it? -- HTH Bob |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Using Month
Hi Bob,
Many thanks for your ongoing support. As you can see it has been more than a few days since my last reply (I took paternity leave and a few weeks holiday from work).... Anyway, I've now array entered the formula and it still doesn't appear to want to work for me. It is quite happy assuming the month is entered, January, February etc but as soon as i use the Yearly Total option or remove the month i get the #VALUE! error. Any suggestions? Many 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 |