count nonblanks and nonblanks in 2 cols with 1 formula

  • Thread starter Thread starter lb
  • Start date Start date
L

lb

I'm trying to figure out 2 different formulas:

1. to count the nonblanks in Col A2:A100 and the nonblanks in Col C2:C100
this will give me the count of those who took the program where it's still
running

2. to count the nonblanks in Col A2:A100 and the blanks in Col C2:C100.
this will give me the count of those who took the program where it is closed

Col A is headed Got Program, indicated by 'Y'
Col C is headed Program Closed, indicated either by blank which means it's
still Open or with a 'C' to indicate the program is closed
(Col B has the Program Staff's name)

thanks
lb
 
=Sumproduct(--(A2:A100="Y"),--(C2:C100=""))
=Sumproduct(--(A2:A100="Y"),--(C2:C100="C"))
 
Hmm, is there a way to do it without using Y or C, just nonblanks for Cols A
and C for the first formula, then nonblanks for Col A and blanks for Col C
for the second formula?

thanks
 
=SUMPRODUCT(--(A2:A100<>""),--(C2:C100<>""))


=SUMPRODUCT(--(A2:A100<>""),--(C2:C100=""))
 
=SUMPRODUCT(--(A2:A100<>""),--(C2:C100<>""))
=SUMPRODUCT(--(A2:A100<>""),--(C2:C100=""))

would be one way
 
Thanks alot Peo - that was just the ticket!

lb

Peo Sjoblom said:
=SUMPRODUCT(--(A2:A100<>""),--(C2:C100<>""))


=SUMPRODUCT(--(A2:A100<>""),--(C2:C100=""))



--

Regards,

Peo Sjoblom

Cols
Click to expand...
 
Back
Top