[HELP]:Count cells in a column if column header passes a check.

[Ozas]

Hello,

I'm usually very good at solving these issues myself, but this is something I have not managed to crack or a couple of days now and I'm getting extremely frustrated. So I think it would be wiser to ask for help than to keep wasting time on my own.

As the illustration below will show, I have a table which shows how many days have been booked for a activity per person during a given month.
What I am trying to do is use the month name holder in the grey cell (which has the code =TEXT(NOW(),"mmm"). ) to act as a control by which the activity booked days will be counted. So for example:

If the current month is ''March'', I want the formula to only count the cells that are not equal to 0 in the column which has the header "Mar".

I have tried everything I could think of and got no results. Perhaps someone here would be kind enough to lend a helping hand.

 

[wigi]

=sum(offset(b2,,month(now()),4))

 

[Ozas]

Ignore the '' days booked this month" field, it should say - number of people that booked days this month.
That is basically the goal, to see how many individuals out of the total that are in the table have booked any number of days in that given month.

 

[MickG]

Have you tried "Dcount"

 

[wigi]

=COUNTIF(offset(b2,,month(now()),4),">0")

 

[Ozas]

Sadly this did not work.

 

[Phanmore]

You could use DCount to solve this. I'm do not use this formula often, so this may not be the best way to do it, but it does work

The formula is:

=DCOUNT(C2:E6,B2,A2:A3)

For this formula to work, you would need to add your criteria into a seperate cell ( i have used cells A2:A3, but you could put this where you like.

Cell A2 = TEXT(NOW(),"mmm")
Cell A3 = >0

 

[Colton]

Excel Workbook
	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	*	Mar	Jan	Feb	Mar	Apr	*
3	*	John	22	16	5	21	*
4	*	Max	0	15	0	17	*
5	*	Nick	0	21	11	19	*
6	*	Fred	12	21	9	0	*
7	*	*	*	*	*	*	*
8	*	*	*	Number:	3	*	*
9	*	*	*	*	*	*	*
Sheet1

 

[Ozas]

You could use DCount to solve this. I'm do not use this formula often, so this may not be the best way to do it, but it does work

The formula is:

=DCOUNT(C2:E6,B2,A2:A3)

For this formula to work, you would need to add your criteria into a seperate cell ( i have used cells A2:A3, but you could put this where you like.

Cell A2 = TEXT(NOW(),"mmm")
Cell A3 = >0

Yep, I have tried this and actually got it to work.
It appears I have not been using the criteria as a table but only as a single cell which the function did not like. Oh well, lesson learned.
Many thanks for your help.

Also, thank you all who replied.