1

# How do I make this formula in Excel?

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?
• Topic Stats
• Top Replies
Type: Discussion • Score: 1 • Views: 1,060 • Replies: 7
No top replies

jespah

1
Thu 19 Jun, 2008 05:17 pm
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.
0 Replies

Wy

1
Thu 19 Jun, 2008 06:23 pm
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!
0 Replies

Chai

1
Thu 19 Jun, 2008 06:26 pm
I am such a nerd.

I'm reading Jespah's formula and nodding my head saying "uh, yeah that's right....huh? oh, ok....."
0 Replies

Wy

1
Thu 19 Jun, 2008 07:00 pm
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...
0 Replies

jespah

1
Fri 20 Jun, 2008 09:23 am
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.
0 Replies

Wy

1
Fri 20 Jun, 2008 01:34 pm
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!

Wy
0 Replies

jespah

1
Fri 20 Jun, 2008 02:25 pm
My pleasure -- it helps keep me sharp.
0 Replies

### Related Topics

Clone of Micosoft Office - Question by Advocate
Do You Turn Off Your Computer at Night? - Discussion by Phoenix32890
The "Death" of the Computer Mouse - Discussion by Phoenix32890
Windows 10... - Discussion by Region Philbis
Surface Pro 3: What do you think? - Question by neologist
Windows 8 tips thread - Discussion by Wilso
GOOGLE CHROME - Question by Setanta
.Net and Firefox... - Discussion by gungasnake
Hacking a computer and remote access - Discussion by trying2learn

1. Forums
2. » How do I make this formula in Excel?