IF AND OR excel statement

IF AND OR Statement, not working need help

  • IF AND or OR function is not giving correct result, need help

    Votes: 0 0.0%
  • IF Logical function

    Votes: 0 0.0%

  • Total voters
    0
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.
 
Last edited by a moderator:
@ 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
 
Last edited:
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.


 
Last edited:
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.
 
AliGW,

Could you please help me on this?
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.

Thank you in advance!!
 

Attachments

  • Count Complete and OS copy Summary.xlsx
    85.5 KB · Views: 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")
 
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")

AliGW, let me clarify may was not clear earlier.

See attached workbook...v2

In summary tab the cell E3 to E14 and F3 to F14 I have manually input the results. I want formulas to pull results from the detail tab.

In cell E3 I want to put number by only counting: C's, Z's, O's and I's.
In cell F3 I want to put number by only counting: C's and Z;s
the reference are in detail tab in col JEN and Status.

for example I will check JEN0034 in both tab then count C,Z,O and I. I have put
7 in E3 (because there are, 5 Z's, 1 C and 1 O; I do not want to count #N/A) and
6 in F3 ( in this cell I have only counted C's and Z's i.e.
5 Z's, 1 C)

See the row 53 of the detail tab has the reference (Column JEN.. is unique in the both tabs)

Thank you

 

Attachments

  • Count Complete and OS copy Summaryv2.xlsx
    85.7 KB · Views: 8
This is getting increasingly complex. Are you going to be introducing any further complexities? If so, it would be easier to know that now before spending even more time on this. You really should give the whole, 'real' picture at the outset.

I will have another look for you.

EDIT: Had another look, and I am afraid I don't have time for this right now. It's a whole new ask. Hopefully someone else will step in.

Good luck with it! :)
 
Last edited:
This is getting increasingly complex. Are you going to be introducing any further complexities? If so, it would be easier to know that now before spending even more time on this. You really should give the whole, 'real' picture at the outset.

I will have another look for you.

EDIT: Had another look, and I am afraid I don't have time for this right now. It's a whole new ask. Hopefully someone else will step in.

Good luck with it! :)

Thank you AliGW.

This is the last whole real picture. there will not be any further addition/complexities to it. I am not in a rush (may be in a week or so)

Thank you again.
 
Back
Top