## View Poll Results: IF AND OR Statement, not working need help

Voters
0. This poll is closed
• IF AND or OR function is not giving correct result, need help

0 0%
• IF Logical function

0 0%
Multiple Choice Poll.

# Thread: IF AND OR excel statement

Originally Posted by AliGW

1. You do not need a + sign at the start of this forumla (I don't know why you added one, but it is not necessary).
Remnants from Lotus123

2. Ah, OK - that explains it.

3. Yes. It did. Thank you so much. but would you explain how it works with your formula? so i can understand the following?. the layout may change in future dont know when.
1. IF(SUM(COUNTIF(\$D5:\$AA5,{"C","Z",""}))
2. -COUNTIFS(\$D\$4:\$AA\$4,"Student",\$D5:\$AA5,"")>=12,"Complete","No")

Edit mod : removed part of the post to create a new thread
Thank you again.

4. @ NK123

When you have a new question it's always best to start a new thread adding links to relevant posts as necessary.
This will get you faster answers as more people will see it.
I deleted your new question from post #23 and started a new thread for you

5. If you change the layout, then the second formula is likely to be broken. However:

=SUM(COUNTIF(\$D5:\$AA5,{"C","Z",""}))

This looks at the range D5:AA5 and returns the number of cells in that range that contain C or Z or are blank.

-COUNTIFS(\$D\$4:\$AA\$4,"Student",\$D5:\$AA5,"")

This counts the number of cells in the range D5:AA5 that are blank if the corresponding cell in the range D4:AA4 says Student. This is then subtracted from the first number, as we don't want to include these in the IF statement.

=IF( ... >=12,"Complete","No")

If formula 1 minus formula 2 is bigger than or equal to 12, return Complete, If not, return No.

6. Pecoflyer was right to move your new query to a new thread, as it bears no relationship whatsoever to this one. I can't help with the new query - sorry.

7. Thank you AliGW and Pecoflyer.

8. You're welcome!

9. AliGW,

Detail tab
I want to count: C, Z, O and I, but I do not want to count #N/A, in column “STATUS”.
C, Z, O, I and #N/A can repeat in status column for JEN.... later on the data will be added in col. V onward to AC (in status and month col).
Summary Tab

1. The results (counted number, except #N/A) should be in the column E. this col represent completed/ incomplete/outstanding.
2. Also under the column F only count C, Z. this represent completed

#N/A represents no transactions, therefore should not be included in counting.

Manually I have filled the results for row 3 to 14 in col E and F
Column JEN.. in both tabs are unique.

10. You just need to add another exclusion criterion:

=IF(SUM(COUNTIF(\$F6:\$AC6,{"C","Z",""}))-COUNTIFS(\$F\$5:\$AC\$5,"Status",\$F6:\$AC6,"",\$F6:\$AC6,NA)>=12,"Complete","No")

Page 3 of 4 First 1 2 3 4 Last