Excel Formula Help

Not sure exactly what you want (an example sheet uploaded would help), but how about:

=IF(AND('2008 R2'!M3="x",'2008 R2'!N3<"Completed"),COUNTIF('2008 R2'!$A$4:$A$1002,"Cat I"),"N/A") 

Select allOpen in new window

---

»bp

Hello, can you post excel file sample?

Here is the file. If you look at the totals page you will see a cell highlighted in yellow. It should read 1 instead of 0 at this point.

thanks for looking at this
STIG-Status-Report---Template.xlsx

0 is correct.  P3 is not less than "Completed" so the condition isn't met to count.

---

»bp

Maybe you need not equal here?

=IF(AND('2008 R2'!M3="x",'2008 R2'!N3<>"Completed"),COUNTIF('2008 R2'!$A$4:$A$1002,"Cat I"),"N/A") 

Select allOpen in new window

I need it so the count for that one would show up as 1 because it is not completed.

anything that is in column N3:N1000 that is other than completed should count as 1

You were on the right track. the solution was

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4<>"Completed"),IF(COUNTA('2008 R2'!$A$4:$A$1002,"Cat I")>0,"1","0"),"N/A")

oops ... i spoke to soon. the solution above still counts as 1 even if '2008 R2'!$A$4:$A$1002,"Cat I" is set to Cat II or anything else. It should only count if it is CAT II.

well after playing with your solution for a sec i got it to work with

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4<>"Completed"),IF(COUNTIFS('2008 R2'!$F$4:$F$1002,"Cat II")>0,"1","0"),"0")

you win the browny points Nikoloz

thanks so much

Did you put the formula in the right cell? The formula I wrote was for G30 in your sample

yes .. i have checked it by changing the data on the 2008 R2 sheet and all is great

Nice to hear you have found solution. You are welcome

Thanks so much guys for the help. I really appreciate it.

well i found an issue guys. if i add a second item to the 2008 R2 sheet it does not count it so i tried

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4:P1000<>"Completed"),IF(COUNTIFS('2008 R2'!$F$4:$F$1002,"Cat II")>0,"1","0"),"0")

any ideas?

Can you post that file again? To check what you changed

here you go
STIG-Status-Report.xlsx

I dont get question exactly, which formula does not work correctly now?

H30?  And what should it count?

ok .let me break it down. This sheet is to track STIGs / Vulnerabilities for my servers. Sheet 1 is obviously for counting totals and the other sheets are for posting the actual STIGs, patches, vulnerabilities, etc. Therfore a sheet like the 2008 R2 may have as many as 1000 items or more by the end of a year.

So on the totals sheet it will look at the 2008 R2 sheet and see if M3 has an "x" in it, meaning that the particular STIG is applicable to that server. It will then look at thr range of N4:N1000 to see if that STIG is completed or not. Then it will look at F4:F1000 to see if it is a CAT 1, Cat 2, or Cat 3 problem. If it is a CAT 1 on that particular server and it has not been completed the total is counted on the totals sheet .

So with the data that is currently on the sheet you have H32 should read a count of 2.

I hope that makes sense.

So with the data that is currently on the sheet you have H32 should read a count of 2. because there are 2 CAT 2 issues for that server on the 2008 R2 sheet.

any thoughts

I am on mobile atm, can not open excel... Can you check this?

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4:P1000<>"Completed"),COUNTIFS('2008 R2'!$F$4:$F$1002,"Cat II"),"0")

Ok .. that counts the Cat I, Cat II, and Cat III isssues correctly but it doesn't change if I mark it "completed".

Looks like your question is more complex.

As I got it, you should check F4 value, if N4 is not marked as completed. F5 value, if N5 is not marked as completed and so on. I will think about this formula.

That didn't work

ok guys .. i tweeked Nikoloz answer just a bit and got it to work.

Thank you so much for your help sir.

Mark, can you post the last formula you got? I want to check what I missed from my point of view. Thanks