Running Total for Unique Variables and a Condition

[Brow5213]

Hello!

I am trying to calculate a running total of specific groups, but I do not want a group to be counted if it does not have a posted date.

Currently, I am using the following CountIF formula: (=COUNTIF($H$6:H7,H7)). This formula is effective in helping me find the running total of unique groups, but it counts a group no matter if it has a posting date or not. I am hoping to have a running count that excludes those groups.

Please see the image for reference.

Thank you so much!

 

[Skybluekid]

Try this

Book1.xlsx
	A	B	C	D
1	Search Stat	Posted Date	Group
2	Not Started		Non-Ops	0
3	endin		Non-ops	0
4	Open	2/21/202.3	Non-ops	1
5	Not Started		Operaciones - Brasil	0
6	Filled	12/5/202.2	Non-ops	2
7	Not Started		Non-ops	2
8	Not Started		Non-ops	2
9	Filled	9/16/202.2	supply Chain	1
10	Upcoming		Shell Rock Plant	0
11	Open		Non-ops	2
12	Open		Non-ops	2
13	Not Started		Non-ops	2
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=COUNTIFS($C$2:C2,C2,$B$2:B2,"<>"&"")

 

[Fluff]

You need to remove the space between the < & > signs.

 

[awoohaw]

try this:

=if(D6="","",Countif($H$6:H6,H6))

the OR is unnecessary.

 

[Brow5213]

Try this

Book1.xlsx
	A	B	C	D
1	Search Stat	Posted Date	Group
2	Not Started		Non-Ops	0
3	endin		Non-ops	0
4	Open	2/21/202.3	Non-ops	1
5	Not Started		Operaciones - Brasil	0
6	Filled	12/5/202.2	Non-ops	2
7	Not Started		Non-ops	2
8	Not Started		Non-ops	2
9	Filled	9/16/202.2	supply Chain	1
10	Upcoming		Shell Rock Plant	0
11	Open		Non-ops	2
12	Open		Non-ops	2
13	Not Started		Non-ops	2
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=COUNTIFS($C$2:C2,C2,$B$2:B2,"<>"&"")

Thanks so much for your response and help. I seem to still be having issues after I've mimicked this formula where it is just returning all zeros. I wrote the function as follows: =COUNTIFS($H$6:H6,H6,$D$6:D6,"< >"&"").

Any guidance is greatly appreciated.

 

[Fluff]

How about

=COUNTIFS($H$6:H6,H6,$D$6:D6,"<>")

 

[Brow5213]

How about

=COUNTIFS($H$6:H6,H6,$D$6:D6,"<>")

Still returning zeros unfortunately.

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Brow5213]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here is a sample of the errors I am seeing. Thank you for your patience and help on this.

Sample for CountIF Running Total.xlsx
	A	B	C	D
1	Search status	Posted Date	Groups	# of Positions Filled by Group
2	not started		Non-Ops	0
3	pending		Non-Ops	0
4	open	2/21/23	Non-Ops	0
5	not started		Operaciones - Brazil	0
6	filled	12/5/22	Non-Ops	0
7	not started		Non-Ops	0
8	not started		Non-Ops	0
9	filled	9/16/22	Supply Chain	0
10	upcoming		Shell Rock Plant	0
11	open		Non-Ops	0
12	open		Non-Ops	0
13	not started		Non-Ops	0
14	filled		Non-Ops	0
15	filled		Non-Ops	0
16	filled		Non-Ops	0
17	not started		Non-Ops	0
18	open		Non-Ops	0
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=COUNTIFS($C$2:C2,C2,$B$2:B2,"< >")

 

[awoohaw]

maybe try this:

=COUNTIFS($C$2:C2,C2,$B$2:B2,">0")