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