What is wrong with this formula?

[ishpahuja]

Hi Super-Brainers

Can you help me decide what is wrong with this formula? It is giving me #value error.

=SUMPRODUCT(1*($D$3:$D$354=$A4),1*(MONTH($E$3:$E$502)=$D$1))

Column D : Stages (e.g. Lead, Opportunity, Converted etc.)
A4 = Lead
Column E: Date corresponding to stage value from column D (e.g. 03-Oct-2019 was the date in column E against 'Lead' in Column D)

D1 = Month value (i.e. 3 for March)

I am trying to calculate the number of Lead (and similarly opportunity, converted in cells A5, A6 etc.) in a particular months (as mentioned in D1). I used this formula and it is giving me error. Can you help find the error?

An extension of this formula, I am trying to solve is to calculate the number of leads in a particular month and year (so the user will have option to choose the year as well along with month - Mar 2018, Mar 2019 etc.)

Thanks in anticipation.

Happy Easter

 

[SpillerBD]

The Array calculation needs arrays with the same size.

=SUMPRODUCT(1*($D$3:$D$[COLOR=#ff0000]354[/COLOR]=$A4),1*(MONTH($E$3:$E$[COLOR=#ff0000]502[/COLOR])=$D$1))