Probably Simple Countif but i can't figure it out

**ssword** · 03-10-2011, 02:22 PM

I've attached a dummy sheet to setup what i need. Basically, I need an easier way to do the following:

Find any row with the weeknum in Test2 corresponding to the weeknum in Test1
Count all of the values marked as "complete" in that row
then Divide by the count of any cell containing something

For 1/3/2011 it should be .92 or 92%

I left the calc i'm current using in Test1!C3 to help you understand what i need but i want it to be more intuitive and copy and paste-able.

**jwright650** · 03-10-2011, 02:36 PM

Originally Posted by ssword

Count all of the values marked as "complete" in that row

Why does your 1st part of the formula reference B6:AZ10....that is looking at 5 rows not a single row.

=COUNTIF(Test2!$B6:$AZ10,C$2)/COUNTA(Test2!$B6:$AZ10)

**ssword** · 03-10-2011, 04:14 PM

Currently i have it count all of the completes in the week by manually referencing the cells. I want it to do exactly this but I want it so it finds this information by using the dates listed instead of manual entry.

**scottylad2** · 03-10-2011, 04:44 PM

Your referencing AZ10 like the last poster said, why? your data stops well short of that?

**Huron** · 03-10-2011, 07:39 PM

I'm sure there must be a simpler formula but this works

=COUNTIF(INDIRECT("Test2!B"&MATCH($B3,Test2!$A$6:$A$30,0)+5&":P"&MATCH($B3+6,Test2!$A$6:$A$30,1)+5),C$2)/COUNTA(INDIRECT("Test2!B"&MATCH($B3,Test2!$A$6:$A$30,0)+5&":P"&MATCH($B3+6,Test2!$A$6:$A$30,1)+5))

**ssword** · 03-12-2011, 10:52 AM

Originally Posted by scottylad2

Your referencing AZ10 like the last poster said, why? your data stops well short of that?

I used AZ10 because I add more columns in the future - might as well add them now. I currently use up to AQ on my non-dummy sheet.

Originally Posted by Huron

I'm sure there must be a simpler formula but this works

=COUNTIF(INDIRECT("Test2!B"&MATCH($B3,Test2!$A$6:$A$30,0)+5&":P"&MATCH($B3+6,Test2!$A$6:$A$30,1)+5),C$2)/COUNTA(INDIRECT("Test2!B"&MATCH($B3,Test2!$A$6:$A$30,0)+5&":P"&MATCH($B3+6,Test2!$A$6:$A$30,1)+5))

This worked with a few alterations. Thanks!