Excel count based on values of 2 columns

Question

I have an Excel 2007 (xlsx) spreadsheet which has a table within it. The table has a column for date and a column for completed. I would like to do a count where the Date is today or earlier and where completed is not "Y"

The syntax I have put together is:

=COUNTIFS(UnitClosures[Completed Y/N], "<>Y", UnitClosures[Dismantling Date],"=< F7")

Cell F7 contains today's date.

When I use this I get 0 back despite having multiple columns not set to completed with a date earlier than today.

How can I change the syntax of my formula to get it to work? Thanks!

change the last bit of your formula to "<="&TODAY()

zx8754
– zx8754

2013-11-19 13:13:12 +00:00
Commented Nov 19, 2013 at 13:13 — zx8754
– zx8754, Commented Nov 19, 2013 at 13:13

barry houdini · Accepted Answer · 2013-11-19 13:11:46Z

6

The final criterion syntax is wrong, F7 has to be outside the quotes and concatenated - try like this:

"=<"&F7

so formula becomes this:

=COUNTIFS(UnitClosures[Completed Y/N], "<>Y", UnitClosures[Dismantling Date],"=<"&F7)

answered Nov 19, 2013 at 13:11

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel count based on values of 2 columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related