ExcelBanter - Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Formula (https://www.excelbanter.com/excel-worksheet-functions/8742-formula.html)

Andy

Formula

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

[email protected]

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?

[email protected]

All times are GMT +1. The time now is 05:52 AM.