SUMPRODUCT - Using Month

[Phendrena]

--(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,

 

[Jarek Kujawa]

try =A1
if it does not work try =A1*1

 

[Jarek Kujawa]

for converting May to try using CHOOSE function

=CHOOSE(A1,"January","February",......................................,"December")

 

[Bob Phillips]

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]

Many thanks Bob, the second one worked as the maonth is entered as normal text.

 

[Phendrena]

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]

This should do what you want

=--(IF(ISNUMBER(A1),YEAR(Cancellation!B16:B23)=(A1),MONTH(Cancellation!B16:B23)=MONTH(DATEVALUE("01"&A1))))

 

[Phendrena]

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]

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)

 

[Bob Phillips]

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]

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,

 

[Phendrena]

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

 

[Bob Phillips]

Did you array-enter it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Phendrena]

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]

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

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

 

[Phendrena]

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,