Count formula (need help)

Solved/Closed
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 4, 2015 at 03:49 PM
 RayH - Nov 4, 2015 at 04:45 PM
Hello, everyone I want to thank you in advance because I know that excel can be frustrating. I hope this is an easy one I have the following data listed in a worksheet and I am trying to use a formula that counts the cells that have a date in them in the row named 'Date lease expires' as a 1 and adds them up.

This is a partial of the worksheet used it starts at row 158 and is column C

"Lease #1
Lessee Name"
"Lease #1
Sponsor Name"
"Lease #1
EDGE Project #"
"Lease #1
Trustee PILOT #"
" Lease #1
Bill of Sale $ Amount "
"Lease #1
Effective Date"
"Lease #1
Lessee Benefit Expires"
"Lease #1
Date Property Transferred to Sponsor/Lessee"

"Lease #2
Lessee Name"
"Lease #2
Sponsor Name"
"Lease #2
EDGE Project #"
"Lease #2
Trustee PILOT #"
" Lease #2
Bill of Sale $ Amount "
"Lease #2
Effective Date"
"Lease #2
Lessee Benefit Expires"
"Lease #2
Date Property Transferred to Sponsor/Lessee"

"Lease #3
Lessee Name"
"Lease #3
Sponsor Name"
"Lease #3
EDGE Project #"
"Lease #3
Trustee PILOT #"
" Lease #3
Bill of Sale $ Amount "
"Lease #3
Effective Date"
"Lease #3
Lessee Benfit Expires"
"Lease #3
Date Property Transferred to Sponsor/Lessee"

=SUM(COUNTIF( INDIRECT({"C164","C173","C182"}),"1"))

Is there a way to copy this formula do the other cells in the row because when I just drag it over it doesn't change the columns when it copies the formula. What am I doing wrong and is there away to have this shorter than it is. Right now I am using 10 different cells that it looks at in my formula.

Thank you for your help

1 reply

Can you explain what it is you are trying to achieve with that formula?
It looks like you are trying to add up all the cells that contain "1" in them. Is that right?
Is this related to your previous question where if the cell is a date then you want to see "1" returned? Ifs so, simply summing that range of cells should give you what you need.
e.g
=SUMIF(O2:O9,">0")
or
=SUM(O2,O5,O8,O10)
0