COUNTIF? IF(COUNTIF())? Help!

[kmclair]

Thanks for taking a peek!

Okay, here is the deal. I have a very large spreadsheet full of parts that I need to perform a general "%complete" type analysis. I have a seemingly simple problem that I just cannot get to work. I need the formula to first check one column to see if it is a "purchase" part, then I need it to count certain parts based on there status. So to explain it a little better, I need the formula to check if it is a certain type of part in one column, then if it falls into a certain set of criteria, count it, in another column.

I have multiple "%complete" sections, so first check of it being "puchase" is from on column continually. The criteria for the subsequent columns will change, per project, but the parts are the same throughout it. It is difficult to explain, if I'm not clear please ask.

My initial attempt was like so... IF(cell="purchase", (COUNTIF(range, "criteria 1" + COUNTIF(same range, "criteria 2"...etc)), "")
...it failed.

HELP! haha

[Jonmo1]

Try

=IF(cell="purchase",SUM(COUNTIF(Range,{"criteria1","criteria2"})),"")

[canapone]

Hi,

maybe a sample file could be very helpful.


You could try COUNTIFS

something to be adjusted like

=COUNTIFS(A:A,"PURCHASE",B:B,"STATUS",C:C,"CRITERIA2")

Just an attempt


Edit: sorry Jonmo1, I did not mean to overlap. Regards

[BeachRock]

Try using COUNTIFS instead of COUNTIF.

If(A1="purchase",COUNTIFS(range1,criteria1,range2,criteria2,range3,criteria3)),"") etc.


It seems I'm a day late and a dollar short.

[kmclair]

The purchase part just checks the cell, the countif criteria are through the range, so I'm running into problems with that.

And I can't really put this online because it has some private info on it. I don't know what would be private really but I'm new so I'm not risking it haha.

[kmclair]

adsfasdf.JPG



This is what I'm working with. Column "1" (between the blacked out areas) is the column that I first have to check to see if it is a purchase part. So basically what I want to happen, is for the specific part number in the far left column for the %complete (circled in black) of the first project (first column to the right of the bigger blacked out area), I want the formula to follow the red arrow. I know this is confusing but for each specific part, first check it is purchase, if not do nothing...if it is, check to see if it follows a certain criterion (different codes on the page...X,R,NA). So, if it is purchase, and then X for that part, return 1. At the end, sum the 1's, divide by the total, and I have my %complete.

Confusing to explain - actually pretty simple, which is what irks me about this problem.

Thanks again!