Using TODAY in a COUNTIFS formula

[spicerguy]

Hi,

I have a list of dates in column A, A1:A365 starting at 1/jan/2012 and ending at the end of the year.
In column B I have an activity marker (text string, "Activity", although this can have other text as well) on various dates and empty cells against dates with no activity.

What I would like to do is have a counter for all the activity in the current month using a single cell formula. I've tried variations on the formula below but whenever I try to use a variation of the "today" call in a formula I get a zero count and have to end up using a reference cell which I have to update every month, which is less than ideal.

The formula I've been trying to get to work with various permutations is in the format

=COUNTIFS(B1:B365,"*",A1:A365,"=MONTH(TODAY())")

Which isn't working for me.

I'm using Excel 2010 on win7.

Any help gratefully received by this board newbie.

 

[Simonc64]

Could you not have the TODAY function in a different cell and LOOKUP that cell in the COUNTIFS formula?

 

[pjmorris]

try this: =SUMPRODUCT(--(MONTH(A1:A365)=MONTH(TODAY())),--((B1:B365)<>""))


SUMPRODUCT multiplies and sums two arrays. The first section MONTH(A1:A365)=MONTH(TODAY()) extracts your date array, compares it to the current month which gives an arrange of False (not in this month) and True (in this month). The double negative here: --(MONTH(A1:A365)=MONTH(TODAY())) converts the TRUE/FALSE values to 1 or 0. The second part of the SUMPRODUCT --((B1:B365)<>"")does something similar but checks that there is something in the cells.

Hope this helps.

Regards

 

[spicerguy]

Simon - thanks for this suggestion, I can see how that would work but still relies on a reference cell. However it's certainly better than what I was using before.

Peter - I've tried this out and it works like a charm. Thank you very much! I'll go and apply it within the workbook I'm doing (which is a little more complex than the example) and then also try and figure out why it works - I'm keen to up my excel chops and not just be a cut&paste kid.

 

[Darren Bartrup]

Peter just beat me to that solution. For help on figuring out how it works have a look at this page: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[pjmorris]

My pleasure. To examine how its working use the Evaluate Formula option on the Formula ribbon, it allows you to decompose the formula and see intermediate results.

 

[spicerguy]

Just an additional question,

I have an additional column with numeric values (column C, C1:C365). These only occur on rows with Activity (ie, cell content in column B), and I would like to do a calculation to sum these numeric values in the current month. I know this is outside of the scope of the thread header, should I post it as a new thread or are people happy to post a reply in this thread? Hopefully the context of the above will provide enough detail.

Many thanks!

 

[barry houdini]

You can just add the range to sum in to SUMPRODUCT, e.g. building on Peter's suggestion

=SUMPRODUCT(--(MONTH(A1:A365)=MONTH(TODAY())),--(B1:B365<>""),C1:C365)

Note that doesn't check the year so I'm assuming you only have data within the current year.

You could also use SUMIFS

=SUMIFS(C1:C365,B1:B365,"<>",A1:A365,">="&EOMONTH(TODAY(),-1)+1,A1:A365,"<"&EOMONTH(TODAY(),0)+1)

 

[spicerguy]

Many thanks Barry, the first option works well for me - thanks for the year caveat as well, your assumption is correct but worth being aware of for future reference.

Thanks to everyone who took the time to help out and those who pointed me to other resources as well.