Link to home
Create AccountLog in
Avatar of Mark Wood
Mark WoodFlag for United States of America

asked on

Excel Formula Help

I need some help with an Excel formula. I need a formula that if cell a = x and cell b < completed then count cell c if it = cat I.

something like this is what I need:  =IFS('2008 R2'!M3="x",'2008 R2'!N3<"Completed",COUNTA('2008 R2'!$A$4:$A$1002 = "Cat I"),"N/A")

Of course the above doesn't work and for some reason I am not figuring this one out.

Any help would be greatly appreciated.
Avatar of Bill Prew
Bill Prew

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") 

Open in new window


»bp
Hello, can you post excel file sample?
Avatar of Mark Wood

ASKER

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") 

Open 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
ASKER CERTIFIED SOLUTION
Avatar of Nikoloz Khelashvili
Nikoloz Khelashvili
Flag of Georgia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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