Hi
Can anyone help. I'm using a excel and using a formula =COUNTIF('2015'!C9:C29,"f")/COUNTA('2015'!C9:C29)
I want to drag the formula across to the next column, but the data it is looking at is =COUNTIF('2015'!F9:F29,"f")/COUNTA('2015'!F9:F29) and it returns for D.
I want to be able to drag it across another 180 columns with it looking at every third column data set .
Hope that makes sense to someone, will be a very long winded way if i have to manually enter every column.
Many thanks
Steve
Excel won't recognize like this
Try this instead
=COUNTIF(INDIRECT("'2015'!"&ADDRESS(2,COLUMN(A1)*3,4)&":"&ADDRESS(9,COLUMN(A1)*3,4)),"f")/COUNTA(INDIRECT("'2015'!"&ADDRESS(2,COLUMN(A1)*3,4)&":"&ADDRESS(9,COLUMN(A1)*3,4)))
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
Or like this
=COUNTIF(OFFSET('2015'!$C$2:$C$9,,(COLUMN(A1)-1)*3),"f")/COUNTA(OFFSET('2015'!$C$2:$C$9,,(COLUMN(A1)-1)*3))
Thanks for those, but they are not returning the right answer.
Any ideas?
Many thanks
They should...Don't know why they aren't working..
Please attach a sample workbook so that I can see what's going wrong!!
You can attach by clicking on the go advanced button below the typing area and then selecting the paper clip icon from the icons above...
Thanks
File attached
Looking at putting the formula in the Availability sheet in C7,the answer should be 76%, but it give 20%
I'm sure its my explanation thats wrong
Many thanks for your help
Try rhis in C7drag and adapt your range to the last column ( first can remain A)
Please Login or Register to view this content.
In your example you told different range C2 to C9 so I made formula accordingly But as you are having C9:C41 in original file
formula should be
=COUNTIF(OFFSET('2015'!$C$9:$C$41,,(COLUMN(A1)-1)*3),"f")/COUNTA(OFFSET('2015'!$C$9:$C$41,,(COLUMN(A1)-1)*3))
Be aware that OFFSET is volatile (INDEX is not). With that many calculations the sheet could slow down
Awesome, many thanks Pepe
Thats worked
You're welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks