SUMIFS not giving desired result

[FredJ]

I have a large sheet of data with dates in column BE (formatted *dd/mm/yyyy) and a score in column CL (formatted Number, zero decimal places). The scores are all whole numbers, 0 thru 4 or the cells are blank. Date cells are also blank where no dates have been provided.

The aim was to produce a summary report of how many scored forms had been returned in the previous month.... easy, Sumproduct

=IF(MONTH(TODAY())=1,SUMPRODUCT(--(MONTH(Forms!$BE$2:$BE$1400)=12),--(YEAR(Forms!$BE$2:$BE$1400)=YEAR(TODAY())-1)),SUMPRODUCT(--(MONTH(Forms!$BE$2:$BE$1400)=MONTH(TODAY())-1),--(YEAR(Forms!$BE$2:$BE$1400)=YEAR(TODAY()))))

Then the powers that be wanted additional data, namely the average score for the forms returned in the previous month. I already know how many forms there were, so it should be simply a matter of calculating the total and dividing by the number of forms. .... SUMIFS I thought

=IF(MONTH(TODAY()=1),SUMIFS(Forms!$CL$2:$CL$1400,MONTH(Forms!$BE$2:$BE$1400),12,YEAR(Forms!$BE$2:$BE$1400),YEAR(TODAY())-1),SUMIFS(Forms!$CL$2:$CL$1400,MONTH(Forms!$BE$2:$BE$1400),MONTH(TODAY())-1,YEAR(Forms!$BE$2:$BE$1400),YEAR(TODAY())))

Unfortunately all I get is "the formula you typed contains an error" and it highlights the entire formula.

Going into the "function" view is no more revealing as it shows that both SUMIFS are in error, but viewing them it shows no errors.

Any assistance gratefully received
Fred

 

[martindwilson]

you cant use a condition on a range in sumifs
SUMIFS(Forms!$CL$2:$CL$1400,MONTH(Forms!$BE$2:$BE$1400)
you would have to use sumproduct

 

[barry houdini]

Hello FredJ, welcome to MrExcel

The previous month starts with

=EOMONTH(TODAY(),-2)+1

and ends with

=EOMONTH(TODAY(),-1) so to sum for all dates in that range use this version of SUMIFS

=SUMIFS(Forms!$CL$2:$CL$1400,Forms!$BE$2:$BE$1400,">="&EOMONTH(TODAY(),-2)+1,Forms!$BE$2:$BE$1400,"<="&EOMONTH(TODAY(),-1))

For the average you can use exactly the same formula but with AVERAGEIFS in place of SUMIFS

 

[FredJ]

Absolutely perfect and 2 new functions to use to boot.

Thanks Barry, much appreciated