Countifs Formula?

[BMcDougall]

I have a worksheet to track progress of a project I'm working on. A sample of it looks something like this:

Zone</SPAN>	Area</SPAN>	Step 1</SPAN>	Step 2</SPAN>	Step 3</SPAN>
1</SPAN>	Room 1</SPAN>	Complete</SPAN>	In Progress</SPAN>	Not Started</SPAN>
1</SPAN>	Room 2</SPAN>	Complete</SPAN>	Not Started</SPAN>	Not Started</SPAN>
1</SPAN>	Room 3</SPAN>	Not Started</SPAN>	In Progress</SPAN>	Not Started</SPAN>
2</SPAN>	Room 1</SPAN>	Complete</SPAN>	Complete</SPAN>	Complete</SPAN>
2</SPAN>	Room 2</SPAN>	In Progress</SPAN>	Not Started</SPAN>	Not Started</SPAN>
2</SPAN>	Room 3</SPAN>	Not Started</SPAN>	Not Started</SPAN>	Not Started</SPAN>
3</SPAN>	Room 1</SPAN>	Complete</SPAN>	Complete</SPAN>	Complete</SPAN>
3</SPAN>	Room 2</SPAN>	Complete</SPAN>	Complete</SPAN>	In Progress</SPAN>
3</SPAN>	Room 3</SPAN>	Not Started</SPAN>	Not Started</SPAN>	Not Started</SPAN>

<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</SPAN>	Not Started</SPAN>	In Progress</SPAN>	Complete</SPAN>	Total % Complete</SPAN>
1</SPAN>	5</SPAN>	2</SPAN>	2</SPAN>	22%</SPAN>
2</SPAN>	5</SPAN>	1</SPAN>	3</SPAN>	33%</SPAN>
3</SPAN>	3</SPAN>	1</SPAN>	5</SPAN>	56%</SPAN>

<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?

 

[Akashwani]

Hi and welcome to MrExcel.

Does this work for you?...

Excel Workbook
	A	B	C	D	E	F
1	Zone	Area	Step 1	Step 2	Step 3
2	1	Room 1	Complete	In Progress	Not Started
3	1	Room 2	Complete	Not Started	Not Started
4	1	Room 3	Not Started	In Progress	Not Started
5	2	Room 1	Complete	Complete	Complete
6	2	Room 2	In Progress	Not Started	Not Started
7	2	Room 3	Not Started	Not Started	Not Started
8	3	Room 1	Complete	Complete	Complete
9	3	Room 2	Complete	Complete	In Progress
10	3	Room 3	Not Started	Not Started	Not Started
11
12
13	Zone	Not Started	In Progress	Complete	Total % Complete
14	1	5	2	2	22.22%
15	2	5	1	3	33.33%
16	3	3	1	5	55.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

 

[kevatarvind]

Excel Workbook
	A	B	C	D	E
1	Zone	Area	Step 1	Step 2	Step 3
2	1	Room 1	Complete	In Progress	Not Started
3	1	Room 2	Complete	Not Started	Not Started
4	1	Room 3	Not Started	In Progress	Not Started
5	2	Room 1	Complete	Complete	Complete
6	2	Room 2	In Progress	Not Started	Not Started
7	2	Room 3	Not Started	Not Started	Not Started
8	3	Room 1	Complete	Complete	Complete
9	3	Room 2	Complete	Complete	In Progress
10	3	Room 3	Not Started	Not Started	Not Started
11
12
13
14	Zone	Not Started	In Progress	Complete	Total % Complete
15	1	5	2	2	22%
16	2	5	1	3	33%
17	3	3	1	5	56%
Sheet1

 

[James Snyder]

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:

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

 

[BMcDougall]

@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.

 

[Akashwani]

Hi,

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

Ak