Solved: countif and offset help for 2 work sheets

[mperrah]

Hello all, it's been a while,

I have a "detail" page that shows qc info for all my techs. Each job has it's own line so there may be several entries for a specific tech.

My "report" page has one row for each tech and counts the number of pass, fail or NA entries from the "detail" page.

I am manual using auto filter and entering the data by hand.

Can I use countif and look up the tech number then check the offset for the columns with pass, fail and NA?

The "report" sheet has a column for each result so I can put a formula in the answer cell for each on the "report" page.

Also, I send the report out each Monday, and keep the detail info for each previous week. So as I auto filter I scroll to the current week.

Can I use a drop down box on the report page with each date range for the year pre-entered to look for the current week range to look up on the detail page?

I'll paste the file.

I think Formulas might do this but the date searching I think would work better with VB - Isn't everything better with VB

Thanks for any insights.
Mark

[mdmackillop]

Try this for part 1. I've made up some named ranges for ease of use. I'm not to clear on the weekly report part.

[mperrah]

Thank you,
The numbers add up good but I need to filter the adding to just a week at a time. I have this formula but am not sure how to apply it for the range:

=IF(E4>C9>(E4-6),COUNTA(G8:G28),"") this counts all jobs in column not just the ones in the range because if true for that cell the whole range gets counted, how do I limit or filter the counting range based on the date test result (offset maybe?)

E4 is the week end date and C:C is the column I want to check against, E4-6 would be the week start

for example: I qc a job thursday 3/22, and my report due monday 3/26 counts the qc's for the previous week (pass, fail, or na; per tech)
E4 says 3/25, E4-6 would be 3/19 any qc done between 3/19 and 3/25 needs to added for the report

[mperrah]

here is a few changes
attached

[mperrah]

On my QCdetail page:
I made a formula look for results within a specific date range and look for an "X" with "AND"
and put a "1" as the true result.
I then have a countif look for "1" to sum the column.

=IF(AND(D8>=$M$3,F8="X",D8<=$N$3),"1","")

=COUNTIF(M8:M5000,"1")

On the WQC page, how can I modify your sumproduct to look within a specified date range?
or can I re-name the ranges you made (Pass, Fail, and NA) as the modified columns

=SUMPRODUCT(--(Techs=$B54),--(Pass="X"))

Thank you for your help.
Mark

[mperrah]

attached my latest attempt
Thank you again

[mdmackillop]

I've added a helper column to use the WeekNum function, which requires the Analysis Toolpak add-in

[Shazam]

Try inputting these formulas in worksheet WQC they dont require a helper column.


Input in cell E15

=SUMPRODUCT(--(Techs=$B15),--(Pass="x"),--(ROUNDUP(((QCDetail!$C$8:$C$100-"1/1")+WEEKDAY("1/1"))/7,0)=QCDetail!$E$3))

Input in cell F15

=SUMPRODUCT(--(Techs=$B15),--(Fail="x"),--(ROUNDUP(((QCDetail!$C$8:$C$100-"1/1")+WEEKDAY("1/1"))/7,0)=QCDetail!$E$3))

Input in cell G15

=SUMPRODUCT(--(Techs=$B15),--(NA="x"),--(ROUNDUP(((QCDetail!$C$8:$C$100-"1/1")+WEEKDAY("1/1"))/7,0)=QCDetail!$E$3))

Input in cell H15

=IF(OR(E15={0,"0"}),"0%",E15/D15)

and copied down.


Hope it helps!

[mperrah]

Thanks to mdmackillop,
Once again you are the one! I never heard of the Weeknum() function.
It works perfectly. I also noticed you made the WQC page numbers update automaticaly based on the tech# within the week range, far above all expectaions.
Thank you soo. much!

Mark

[mperrah]

I could not find sumproduct in help, can you explain what it does? It looks like part of an aray, but its checking not multiplying?
Mark

[mdmackillop]

Have a look at XLD's excellent article here.