Reply
Thu 19 Jun, 2008 04:24 pm
Hi. I'm trying to write a formula to track the actual number of OK items we produce during the course of a job, broken down by rounds. I think I want a formula that counts everything in a range except blanks, redo, and OK.
Here's my thinking:
We have a spreadsheet that identifies each item needed for the finished piece in a column. Following columns show spaces for Round 1, Round 2, etc. The four of us who are responsible for checking initial the column next to the identifier when we pass the product. Others can help, and use their own initials. This is my sticking point.
Each item goes for one more check, and can be returned to us to start over. We replace our initials with "redo" to show that it's being done over. When a product is accepted we put OK in the column.
I have no problem with my formulas that tell us how many items are left to produce -- that's a count of blank cells plus a count of redo cells.
What I have to do now is find sums that tell me how many items in each column were produced.
That's why what I think I want is a formula that counts everything in a range except blanks, redo, and OK. I'd do this for each column, then sum the answers.
Suggestions?
Make another column, to the right of the column where you want to count. Put this formula into the cel in the second row of that column. I am using the B column as where you've got the notations of "OK", "redo" or blanks so if you're using the C column you'll need to change the formula accordingly.
=IF(OR(COUNTIF(B2,"OK"),COUNTIF(B2,"redo"),COUNTIF(B2,"")),1,0)
In the cel where you've typed the formula, move your cursor until it becomes a black cross. Click and drag as far down as you need the calculating formulae. Then, all the way at the bottom of the column where the formulas are, click in the cel just below that and hit the sigma key to total it.
Bless you! I understand what you wrote; someday, if I keep it up, I will know how to write that. It's not second nature yet; I was looking at COUNTIFS and trying to say "count this and this and this"... didn't work.
Thank you Jespah!
I am such a nerd.
I'm reading Jespah's formula and nodding my head saying "uh, yeah that's right....huh? oh, ok....."
I gave it a quick try and it seems to work backwards. None of those arguments were in my cel, but initials were, and it returned 0. I need it to return 1, so when I total the column I get the number of items that have been initialed, not only by the four of us, but by anyone else who steps in to help out...
I don't really have time to play with it this minute (maybe in a half-hour oor so) but I think I can get it to work...
Ooh oops I counted all of the stuff you didn't want counted. Here's the reverse formula, to count everything BUT OK, blank or redo:
=IF(OR(COUNTIF(B2,"OK"),COUNTIF(B2,"redo"),COUNTIF(B2,"")),0,1)
Essentially what it does is, if the cel contains OK, redo or nothing, it counts it as 0. Everything else, it counts as a 1.
Let me know if I'm still not gettin' it.
Jespah, that's exactly it. I'm just getting back to this (I don't get to work until 11). Amd that's the first thing I was going to try -- switching the returns.
I used to write formulas like this all the time but it hasn't been needed for about five years. I'm seriously out of practice!
Thanks for your help!
Wy
My pleasure -- it helps keep me sharp.
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace