Formula

[Andy]

I have the following formula input in order to bring back
the month and number of days:-
="CC"&D2&" month of "&TEXT(E2,"mmmm-yyyy")&" =
"&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=E2)=MONTH
(E2)),C2:C100)&" Days
"&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=F2)=MONTH
(F2)))&" occasions"

However it brings back 0 Month and 0 days even through
there are days and months in the cells.

Any help please.

from the above info i am going to try and insert a chart,
any help in this area would be appreciated

 

[hrlngrv]

Andy wrote...

I have the following formula input in order to bring back
the month and number of days:-

="CC"&D2&" month of "&TEXT(E2,"mmmm-yyyy")&" = "
&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=E2)=MONTH(E2)),
C2:C100)&" Days "
&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=F2)=MONTH(F2)))
&" occasions"

However it brings back 0 Month and 0 days even through
there are days and months in the cells.

....

The problem terms are

MONTH(B2:B100=E2)=MONTH(E2)

and

MONTH(B2:B100=F2)=MONTH(F2)

B2:B100=E2 will return an array of boolean (True/False) values, and
MONTH(TRUE) = MONTH(FALSE) = 1. If MONTH(E2) > 1, then the terms above
will be arrays of all zeros; otherwise, arrays of all ones. In both
cases the results are likely to be incorrect.

Did you mean
MONTH(B2:B100)=MONTH(E2)

and

MONTH(B2:B100)=MONTH(F2)

respectively?

 

[hrlngrv]

Andy wrote...

I have the following formula input in order to bring back
the month and number of days:-

="CC"&D2&" month of "&TEXT(E2,"mmmm-yyyy")&" = "
&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=E2)=MONTH(E2)),
C2:C100)&" Days "
&SUMPRODUCT(--(A2:A100=D2),--(MONTH(B2:B100=F2)=MONTH(F2)))
&" occasions"

However it brings back 0 Month and 0 days even through
there are days and months in the cells.

....

The problem terms are

MONTH(B2:B100=E2)=MONTH(E2)

and

MONTH(B2:B100=F2)=MONTH(F2)

B2:B100=E2 will return an array of boolean (True/False) values, and
MONTH(TRUE) = MONTH(FALSE) = 1. If MONTH(E2) > 1, then the terms above
will be arrays of all zeros; otherwise, arrays of all ones. In both
cases the results are likely to be incorrect.

Did you mean
MONTH(B2:B100)=MONTH(E2)

and

MONTH(B2:B100)=MONTH(F2)

respectively?