Mark Wood

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.

something like this is what I need: =IFS('2008 R2'!M3="x",'2008 R2'!N3<"Completed",COUNTA(

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

Any help would be greatly appreciated.

Hello, can you post excel file sample?

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

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

ASKER

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4<>"Completed"),IF(CO

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.

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

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4<>"Completed"),IF(CO

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

ASKER

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

ASKER

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

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?

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4:P1000<>"Completed")

any ideas?

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

ASKER

here you go

STIG-Status-Report.xlsx

STIG-Status-Report.xlsx

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

H30? And what should it count?

H30? And what should it count?

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.

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.

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.

ASKER

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

=IF(AND('2008 R2'!O3="x",'2008 R2'!P4:P1000<>"Completed")

ASKER

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.

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

That didn't work

ASKER

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

Thank you so much for your help sir.

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

Open in new window

»

bp