Simple formula ?

[fireslguk]

I have a table with column a:a dates
Column b with status codes e.g W

I want to produce a report on the total number of say “W” in a set period

So for example

1/1/18 until 30/1/18. Equals 4
1/2/18 until 28/2/18. Equals 1

And so on.

The formula on a different sheet looking at tab “2018-2019”

 

[steve the fish]

You need a COUNTIFS. It is a fairly simple formula. Something like:

=COUNTIFS(B:B,"W",A:A,">="&date1,A:A,"<="&date2)

Replace date1 and date2 with cell references that contain your dates in question.

 

[fireslguk]

You need a COUNTIFS. It is a fairly simple formula. Something like:

=COUNTIFS(B:B,"W",A:A,">="&date1,A:A,"<="&date2)

Replace date1 and date2 with cell references that contain your dates in question.

A zero return mmmmm what’s going’s wrong

 

[steve the fish]

Paste your formula.

 

[fireslguk]

Paste your formula.

Funny things happening lol

I discovered blank cells no dates of that makes a difference in the A:A column so put some in - can cells be blank ?


After dragging the auto fill this copied the formula however returned all zeros, then when I clicked on the formula bar and pressed enter the formula worked and returned a number so not sure why the auto fill did that

 

[nikio8]

make sure each cell is pointing to the right range. you have to make a new table

W E R T Y
date1 date2
date date
date date

C2 =COUNTIFS(Sheet1!B:B,B$1,Sheet1!A:A,">=A$2,Sheet1!A:A,"<=B$2)

drag down and across. check for changes

 

[fireslguk]

All sorted now, was someone else’s sheet and I didn’t realise they had on calculation manual !!