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.
Microsoft Excel

Last Comment
Nikoloz Khelashvili

8/22/2022 - Mon
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")
``````

»bp
Nikoloz Khelashvili

Hello, can you post excel file sample?
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
Bill Prew

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

»bp
Nikoloz Khelashvili

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")
``````
Mark Wood

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

ASKER
anything that is in column N3:N1000 that is other than completed should count as 1
ASKER CERTIFIED SOLUTION
Nikoloz Khelashvili

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Mark Wood

ASKER
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")
Mark Wood

ASKER
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.
Mark Wood

ASKER
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
Nikoloz Khelashvili

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

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

Nice to hear you have found solution. You are welcome
Mark Wood

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

ASKER
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?
Nikoloz Khelashvili

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

ASKER
Nikoloz Khelashvili

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

H30?  And what should it count?
Mark Wood

ASKER
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.
Mark Wood

ASKER
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.
Mark Wood

ASKER
any thoughts
Nikoloz Khelashvili

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

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

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
Nikoloz Khelashvili

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Mark Wood

ASKER
That didn't work
Mark Wood

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

Thank you so much for your help sir.
Nikoloz Khelashvili

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