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,