Counting Blanks as Zeroes

[USAFMichael]

Hello All,

I have a workbook that non-Excel people use. It records the progress of the elevator car we're installing. It's a free-form table where users can enter their car IDs anywhere in the "Car" column (see the example). I want to count cars "not started" as a data point. Unfortunately users, aren't always putting a "zero" and are leaving the "progress" column blank to represent "zero progress". I want to be able to work around this, since the countif function ignores blanks.

I think there has to be a way to use the "car" column to determine if the "progress" column should be counted. Essentially, if there's data in the "car" column and a blank in the associated "progress" column, count that as a zero (and obviously also count the actual zeroes).

In both examples, "Cars not started" should equal "3".

Sorry if I omitted any required info.

Thanks in advance!

Michael

 

[aaewalsh]

Why don't you put in a formula like this in cell E5 and fill down

=if(D5<>"",0,"")

 

[aaewalsh]

Or we could create a macro to do it also

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5		3		A	0%	E	100%
6				B
7				C	25%	F	100%
8				D	80%	G
Lists

Cell Formulas
Range	Formula
B5	B5	=SUM(COUNTIFS(D5:F8,"?*",E5:G8,{0,""}))

 

[USAFMichael]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5		3		A	0%	E	100%
6				B
7				C	25%	F	100%
8				D	80%	G
Lists

Cell Formulas
Range	Formula
B5	B5	=SUM(COUNTIFS(D5:F8,"?*",E5:G8,{0,""}))

Thanks for both the warm-welcome and the solution Fluff! I can barely understand your voodoo magic, but it works!!!

If you have a moment, would you mind walking me through the formula? I can see that you're using wildcards and array brackets, but don't quite get how it all works together.

Cheers!

Michael

 

[USAFMichael]

Or we could create a macro to do it also

I appreciate the assist! I wasn't clear in my description, but users have to enter data in the E column and I'm avoiding macros in this workbook. Decided to go with Fluffs idea.

 

[Fluff]

Glad we could help & thanks for the feedback.
The "?*" will count any cell with text & the {0,""} will count cells that are either 0 or blank

 

[aaewalsh]

I appreciate the assist! I wasn't clear in my description, but users have to enter data in the E column and I'm avoiding macros in this workbook. Decided to go with Fluffs idea.

No problem at all, glad you got it sorted