Excel formula help: Google has failed me.

Greboth · 14 Nov 2013 at 14:49

Afternoon all

Help me OCUK, you're my only hope!

I am trying to count the number of occurances of events in each month. I have a total log for the year but need to count the number of times things occur in a row. The log records
Column B: MIN, MIS, MIB etc
Coumn G: Date
Column J:Yes or No (depends on other aspects of the sheet)

I need a formula which in word form it would read "Count where in the same row "MIN" is in column B, Month January in column G and "Yes" is in column J"

This could then be duplicated for MIS, MIB and every other month.

I got the formula
=COUNTIFS(B2:B13,"MIN",G2:G13,MONTH(G2:G13)=1,J2:J13,"Yes")

It doesn't work though, the problem seems to be that you can't put a range in for the Month= and I cannot find a solution to this

gillywibble · 14 Nov 2013 at 15:32

Have you tried using a pivot table?

DanielMMS · 14 Nov 2013 at 15:38

Could you create an additional column, say K, that extracts the month from G? Then just replace the G expression with K2:K13,1

estebanrey · 14 Nov 2013 at 15:38

=COUNTIFS(B2:B13,"MIN",G2:G13,">" & 1/1/2013,G2:G13,"<" & 1/2/2013,J2:J13,"Yes")

This counts where 'MIN', a the date range of January 2013 and 'Yes' and in the same row.

Greboth · 14 Nov 2013 at 16:21

Brilliasnt! Thanks for the help guys

gillywibble said:
Have you tried using a pivot table?

No, never haved used pivot tables though so many people mention them I feel I should work out what they are. It might save me a lot of trouble.

estebanrey said:
=COUNTIFS(B2:B13,"MIN",G2:G13,">" & 1/1/2013,G2:G13,"<" & 1/2/2013,J2:J13,"Yes")

This counts where 'MIN', a the date range of January 2013 and 'Yes' and in the same row.

I really over complicated this, never thought of doing it like this. I couldn't get it to work with dates but in the 'stats' section the period are listed instead of just the month so just pointed to those cells.