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,

SUMPRODUCT - Using Month
 

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

SUMPRODUCT - Using Month
 

for converting May to try using CHOOSE function

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

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,

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,

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,

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,

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,

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,

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,

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,

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,

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,

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

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

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,