CODEWITHSUNDEEP
excelexcel-formula
Motorhead1308
Motorhead1308

Reputation: 183

How do I write an excel formula to count the number of a value in one column based off of a date in another column?

I have a worksheet and I'm trying to do a simple Count function, probably a countif but I'm unsure how to go about writing the formula.

I have two columns that I'd like to use for this formula.

Column N - I would like to filter for the Criteria of "C" or anytime a cell has a value of C

Column 0 - This column has dates filled in (short date format).

I would like to get a count of every C for each month, as simple as that.

enter image description here

In this example I filtered the date for May of 2017 and filtered for C under the Check column. We can see that there are 12 instances of C showing in the month of May 2017.

Does anyone know how to structure a formula that I would be able to Count the Number of C's for every month into the foreseeable future?

I figured out how to count the total present in a date range but unsure of how to add the date range plus Column N (Check) every time "C" is present in the cell.

=SUMPRODUCT((O:O>=DATEVALUE("5/1/2017"))*(O:O<=DATEVALUE("5/31/2017")))

Upvotes: 0

Views: 101

Answers (3)

D G
D G

Reputation: 9

Try this....you need to select the entire Column B and named the column as 'Date'.enter image description here

Upvotes: -1

dazzathedrummer
dazzathedrummer

Reputation: 531

In another column (lets say 'P' for example) I would insert a formula to give you the month number =Month(P7) - this will return 5 for May.

I would then use COUNTIFS (Like COUNTIF but it uses multiple criteria) to count where column N contains 'C' and column 'P' contains '5'.

=COUNTIFS(N:N,"C",P:P,5)

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

Try this

=COUNTIFS(O1:O100,">="&A1,O1:O100,"<"&B1,N1:N100,"C")

Where A1 has the start date and B1 has the end date for that month. You can use DATEVALUE() instead of A1 and B1. Change as applicable

Screenshot enter image description here

If you want to use SUMPRODUCT then see this

=SUMPRODUCT((O:O>=DATEVALUE("1/5/2017"))*(O:O<=DATEVALUE("30/5/2017"))*(N:N="C"))

Upvotes: 2

Related Questions