count nonblanks and nonblanks in 2 cols with 1 formula

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
 
T

Tom Ogilvy

=Sumproduct(--(A2:A100="Y"),--(C2:C100=""))
=Sumproduct(--(A2:A100="Y"),--(C2:C100="C"))
 
L

lb

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
 
P

Peo Sjoblom

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


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

Dave Peterson

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

would be one way
 
L

lb

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Critical Counitif formula 3
function returning SUM of 2 adjacent cols in 2 separate cells 7
Search between dates & return other column 8
Sum if Critea 1 and critera 2 are met 6
Count no. of occurance in 2 dynamic columns within a data period 7
Conditional count across 2 columns 3
Formula printing in cell 6
Count the letters in the cells and sum in another cell 1

Top