# If & COUNTIF Statements for Productivity

#### tspehar21

##### New Member
For my work my productivity is tracked and recorded, however the database software our company uses is garbage and I have an excel spreadsheet that I prefer to use.

So what I track is:
• Encounter #
• If it is a NB (NewBorn), Y for yes, nothing if not
• The LOS (Length Of Stay), #

Depending on the LOS and if it is a NB or not, will depict how many minutes you are allotted for that encounter. For instance a 1-3 day LOS you are given 32.5 min, whereas a 1-3 day LOS NB you are given 22.5 min.

What I want to be able to do is have excel count up how many 1-3 day LOS encounters I have for the week, how many 1-3 day NB LOS encounters, 4-6 day LOS, etc.

I was thinking that this would involve an IF statement to determine if column 2 has a "Y" making it a NB encounter, and then the COUNTIF statement to count how many encounters are 1-3 day LOS (Column 3).

 Encounter# NB LOS 23456 2 34567 Y 1 45678 Y 2 56789 3 67890 Y 2 78901 Y 1 89012 Y 1 90123 4 01234 3

<tbody>
</tbody>

For the above table, there are:
5x 1-3 day LOS NB encounters, (1, 2, 2, 1, 1)
3x 1-3 day LOS encounters (2, 3, 3)
1x 4-6 day LOS encounters (4)

How would you write a formula that would be able to Count each of the above values? I realize I would need a separate formula for each value range, 1-3 day LOS, 1-3 day LOS NB, 4-6 day LOS. Would I have to have a separate formula for 1 day LOS, 2 day LOS, 3 day LOS, then SUM those values? I need to be able to keep the NB encounters separate from the normal ones as they are given a different amount of time, hence my thought for the IF statement. Please let me know what you think.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

Try something like this

 A​ B​ C​ D​ E​ F​ G​ H​ 1​ Encounter#​ NB​ LOS​ DaysMin​ DaysMax​ NB Y?​ Result​ 2​ 23456​ 2​ 1​ 3​ Y​ 5​ 3​ 34567​ Y​ 1​ 1​ 3​ 3​ 4​ 45678​ Y​ 2​ 4​ 6​ Y​ 0​ 5​ 56789​ 3​ 4​ 6​ 1​ 6​ 67890​ Y​ 2​ 7​ 78901​ Y​ 1​ 8​ 89012​ Y​ 1​ 9​ 90123​ 4​ 10​ 1234​ 3​

Criteria in columns E:G

Formula in H2 copied down
=COUNTIFS(C\$2:C\$10,">="&E2,C\$2:C\$10,"<="&F2,B\$2:B\$10,IF(G2="","",G2))

Hope this helps

M.

Replies
0
Views
39
Replies
5
Views
68
Replies
5
Views
104
Replies
7
Views
42
Replies
1
Views
37