I am trying to create a formula that combines the COUNTBLANK function with a
COUNTIF (or SUMIF)-type function using the following Excel data:

A B C D

3 D800 1 TP
3 D801 2 TP
3 D802 3 TP
3 D803 4 TP
3 D804 5 TP
3 D805 6 TP
3 D806 7 TP
3 D807 8 TP
9 D808
9 D809
9 D80A F-SYS 1
9 D80B F-SYS 2
9 D80C F-SYS 3
9 D80D F-SYS 4
9 D80E F-SYS 5
9 D80F F-SYS 6
9 D810 F-SYS 7
9 D811 F-SYS 8
9 D812 F-SYS 9
9 D813 F-SYS 10
9 D814 F-SYS 11

What I am attempting to do is count the number of blank cells in Col. "C"
when the value in Col. "A" is "9" (or "3", or whatever value I am comparing
in that Col.)

I have not been able to any combination of the functions to give me a valid
result - in the case I mentioned above, the result shoud be "2", but I
eitheer get "0" or some other much larger number.

Anyone have any thoughts on how to accomplish this??

Thanks!!

DLT

=SUMPRODUCT(--(A1:A500=9),--(C1:C5000="")) will return number of rows with
blank in column C and 9 in column A.

"DLT" wrote:

I am trying to create a formula that combines the COUNTBLANK function with a
COUNTIF (or SUMIF)-type function using the following Excel data:

A B C D

3 D800 1 TP
3 D801 2 TP
3 D802 3 TP
3 D803 4 TP
3 D804 5 TP
3 D805 6 TP
3 D806 7 TP
3 D807 8 TP
9 D808
9 D809
9 D80A F-SYS 1
9 D80B F-SYS 2
9 D80C F-SYS 3
9 D80D F-SYS 4
9 D80E F-SYS 5
9 D80F F-SYS 6
9 D810 F-SYS 7
9 D811 F-SYS 8
9 D812 F-SYS 9
9 D813 F-SYS 10
9 D814 F-SYS 11

What I am attempting to do is count the number of blank cells in Col. "C"
when the value in Col. "A" is "9" (or "3", or whatever value I am comparing
in that Col.)

I have not been able to any combination of the functions to give me a valid
result - in the case I mentioned above, the result shoud be "2", but I
eitheer get "0" or some other much larger number.

Anyone have any thoughts on how to accomplish this??

Thanks!!

DLT

Thanks! That works exactly as I needed it to!!

"Sean Timmons" wrote:

=SUMPRODUCT(--(A1:A500=9),--(C1:C5000="")) will return number of rows with
blank in column C and 9 in column A.

"DLT" wrote:

I am trying to create a formula that combines the COUNTBLANK function with a
COUNTIF (or SUMIF)-type function using the following Excel data:

A B C D

3 D800 1 TP
3 D801 2 TP
3 D802 3 TP
3 D803 4 TP
3 D804 5 TP
3 D805 6 TP
3 D806 7 TP
3 D807 8 TP
9 D808
9 D809
9 D80A F-SYS 1
9 D80B F-SYS 2
9 D80C F-SYS 3
9 D80D F-SYS 4
9 D80E F-SYS 5
9 D80F F-SYS 6
9 D810 F-SYS 7
9 D811 F-SYS 8
9 D812 F-SYS 9
9 D813 F-SYS 10
9 D814 F-SYS 11

What I am attempting to do is count the number of blank cells in Col. "C"
when the value in Col. "A" is "9" (or "3", or whatever value I am comparing
in that Col.)

I have not been able to any combination of the functions to give me a valid
result - in the case I mentioned above, the result shoud be "2", but I
eitheer get "0" or some other much larger number.

Anyone have any thoughts on how to accomplish this??

Thanks!!

DLT