# Countifs Formula?

#### BMcDougall

##### New Member
I have a worksheet to track progress of a project I'm working on. A sample of it looks something like this:
 Zone Area Step 1 Step 2 Step 3 1 Room 1 Complete In Progress Not Started 1 Room 2 Complete Not Started Not Started 1 Room 3 Not Started In Progress Not Started 2 Room 1 Complete Complete Complete 2 Room 2 In Progress Not Started Not Started 2 Room 3 Not Started Not Started Not Started 3 Room 1 Complete Complete Complete 3 Room 2 Complete Complete In Progress 3 Room 3 Not Started Not Started Not Started

<TBODY>
</TBODY>

What I want to do is create a totals sheet that counts cells if the zone range (A2:A10) equals 1,2, & 3 and if the range of steps (C2:E10) equals "Not Started", "In Progress", & "Complete". The sheet would look something like this:
 Zone Not Started In Progress Complete Total % Complete 1 5 2 2 22% 2 5 1 3 33% 3 3 1 5 56%

<TBODY>
</TBODY>

I could isolate each range of cells for each zone and use a countif formula but my real sheet has 1695 cells to incorperate and the possibility of adding/removing rows. I assume I would be able to incorperate the entire range of cells into a countifs formula but cannot figure out how. Any suggestions?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi and welcome to MrExcel.

Does this work for you?...

Excel Workbook
ABCDEF
1ZoneAreaStep 1Step 2Step 3
21Room 1CompleteIn ProgressNot Started
31Room 2CompleteNot StartedNot Started
41Room 3Not StartedIn ProgressNot Started
52Room 1CompleteCompleteComplete
62Room 2In ProgressNot StartedNot Started
72Room 3Not StartedNot StartedNot Started
83Room 1CompleteCompleteComplete
93Room 2CompleteCompleteIn Progress
103Room 3Not StartedNot StartedNot Started
11
12
13ZoneNot StartedIn ProgressCompleteTotal % Complete
14152222.22%
15251333.33%
16331555.56%
17
Sheet2

The formula in B14 needs to be copied across and down, the formula in E14 needs to be copied down.
You will need to change the cell references to suit your layout.

I hope that helps.

Ak

Excel Workbook
ABCDE
1ZoneAreaStep 1Step 2Step 3
21Room 1CompleteIn ProgressNot Started
31Room 2CompleteNot StartedNot Started
41Room 3Not StartedIn ProgressNot Started
52Room 1CompleteCompleteComplete
62Room 2In ProgressNot StartedNot Started
72Room 3Not StartedNot StartedNot Started
83Room 1CompleteCompleteComplete
93Room 2CompleteCompleteIn Progress
103Room 3Not StartedNot StartedNot Started
11
12
13
14ZoneNot StartedIn ProgressCompleteTotal % Complete
15152222%
16251333%
17331556%
Sheet1

What I want to do is create a totals sheet that counts cells if the zone range (A2:A10) equals 1,2, & 3 and if the range of steps (C2:E10) equals "Not Started", "In Progress", & "Complete".

OK, just pseudocode here, but I am thinking it could be done like this:
Code:
``````For i = 1 to Zones
CountIFS("A2:A" & row(xlup), ("A2:A" & row(xlup).Value = i), ("B2:B" & row(xlup), ("B2:B" & row(xlup).Value = "Not Started")))
CountIFS("A2:A" & row(xlup), ("A2:A" & row(xlup).Value = i), ("B2:B" & row(xlup), ("B2:B" & row(xlup).Value = "In Progress")))
CountIFS("A2:A" & row(xlup), ("A2:A" & row(xlup).Value = i), ("B2:B" & row(xlup), ("B2:B" & row(xlup).Value = "Completed")))
...)
Next i``````

Last edited:
@Akashwani
Thanks for the welcome! This formula works perfectly, I wouldn't have thought to use a sumproduct.

@kevatarvind
This one also works just as well, this is closer to what I was thinking.

@James Snyder
Sorry man, I can't figure out how to get yours to work, but that's most likely because I'm a noob. Thanks though.

Last edited:
Hi,

I'm pleased it worked for you and thanks for the feedback.

Ak

Replies
0
Views
465
Replies
3
Views
155
Replies
0
Views
192
Replies
2
Views
147
Replies
0
Views
805

1,203,632
Messages
6,056,439
Members
444,864
Latest member
Thundama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back