Count number of occurrences in month (google sheet)

[Juliansmity]

Hi

I can't work out how to count number of occurrences in a month, I have worked out how to sum the total £ for a month. (See column T to V)

Also is there away in which you can not have sum calculate unless its the current month. For example August is showing £0 but that is only because no data exists for august yet. only way I can do it presently is to delete formula.

https://docs.google.com/spreadsheets...it?usp=sharing


Thanks
Julian

[FDibbins]

I cannot load google docs (from work), but it sounds like you need to use =countifS()

[Juliansmity]

Hi,

nope can't work out how to get countifs to work

I've attached an excel version if that helps


CURRENT DRAFT WORKING VERSION Operations Asphalt Material Complaint Dashboard.xlsxCURRENT DRAFT WORKING VERSION Operations Asphalt Material Complaint Dashboard.xlsx

[Special-K]

It would have been best to have removed/amended any sensitive data before posting the spreadsheet.

Regarding zeroes showing in August

=IF(your formula=0,"",your formula)

Column O is defined as TEXT. SUMPRODUCT will not work, the column must consist of numbers ONLY.

[Juliansmity]

Hi data isn't sensitive but thanks for reminder for future

The zeros are not a big deal, I've altered Column O to be currency only.

It was more how would i be able to count number of occurrences in month, I've tried COUNTUNIQUE also but to no avail.

[Jonmo1]

Try

=COUNTIFS($B$18:$B$100,">="&$S6,$B$18:$B$100,"<"&EOMONTH($S6,0)+1)
and
=SUMIFS($O$18:$O$100,$B$18:$B$100,">="&$S6,$B$18:$B$100,"<"&EOMONTH($S6,0)+1)


And to do just the current month

=COUNTIFS($B$18:$B$100,">="&EOMONTH(TODAY(),-1)+1,$B$18:$B$100,"<"&EOMONTH(TODAY(),0)+1)
and
=SUMIFS($O$18:$O$100,$B$18:$B$100,">="&EOMONTH(TODAY(),-1)+1,$B$18:$B$100,"<"&EOMONTH(TODAY(),0)+1)

[Juliansmity]

Spot on Thank you very much

Try

=COUNTIFS($B$18:$B$100,">="&$S6,$B$18:$B$100,"<"&EOMONTH($S6,0)+1)
and
=SUMIFS($O$18:$O$100,$B$18:$B$100,">="&$S6,$B$18:$B$100,"<"&EOMONTH($S6,0)+1)


And to do just the current month

=COUNTIFS($B$18:$B$100,">="&EOMONTH(TODAY(),-1)+1,$B$18:$B$100,"<"&EOMONTH(TODAY(),0)+1)
and
=SUMIFS($O$18:$O$100,$B$18:$B$100,">="&EOMONTH(TODAY(),-1)+1,$B$18:$B$100,"<"&EOMONTH(TODAY(),0)+1)

[Jonmo1]

You're welcome.