Count formula (need help) [Solved/Closed]

Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
-
 RayH -
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)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!