using countif with multiple corresponding criteria

[macmandan1]

Ok, what I'm trying to do is to make a logbook for a machining center. Each part has an op10 and an op20, essentially front and back. And each part number falls into the category of OS or FS. I've used AND logic to make tables in hidden columns to be used by a countif statement to determine my totals.
I.e. to determine if a scroll is completed, op20 has a a value of 1 AND column C is "OS".
I use
=IF(AND(A9=1;C9="OS");1;" ")
Then I countif criteria is 1 in the column i created with that statement.

That works just fine.
Now what I want to do is to be able to create daily totals of OS and FS by simply modifying a variable date in a formula. So I'd like to essentially say:
Countif Column C =OS and Corresponding column D = 1, and corresponding Shift date = 10.02.12(date to be variable).

I'm at a wall here. Is there any way to do this somewhat simply?
\1

[teylyn]

Hello macmandan1,

welcome to the forum.

With countif, you're pretty much at the limit of what Excel 2003 can do for you. With 2007, you could use the new COUNTIFS function to include more conditions.

In 2003 you can use SUMPRODUCT to achieve the same thing, althoug it is somewhat slower than COUNTIFS when using very large data sets.

You're probably looking at a formula along the lines of

=sumproduct(--($C$9:$C$100="OS"),--($D$9:$D$100=1),--($H$9:$H$100=$L$9))

Adjust ranges to suit.

To find out how SUMPRODUCT works, check out this article; http://xldynamic.com/source/xld.SUMPRODUCT.html

hth

[macmandan1]

Thanks for the help. That looks like it puts me on the right track at least.

That particular function doesn't like my "N/A" cells that I get as in the "OS", or "FS". That particular Coumn, column C, uses the formula:
=VLOOKUP(B9,'Reference List'!$A$1:$B$36,2,FALSE)
So it's dependent on the B column.

I'll also have to see about getting this workstation updated to Excel 2007, as we do have it on other computers in the factory.

Thanks for your help, and any more feedback is appreciated.

[teylyn]

Hi,

you can avoid the NA error if you replace your Vlookup formula with this

=if(isna(VLOOKUP(B9,'Reference List'!$A$1:$B$36,2,FALSE)),"",VLOOKUP(B9,'Reference List'!$A$1:$B$36,2,FALSE))

Then the Sumproduct should also work without errors.

[macmandan1]

This is exactly what I was looking for. Using that exact formula, and removing the $$ from the date, I'm able to copy and paste my Daily production box from day to day, only replacing the date, and the rest of the formula stays the same.

I also used a vlookup function so that this data will automatically populate my weekly production spreadsheet.

I appreciate the help!

[teylyn]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save