IF(OR... not working

[Pjcan1]

So i have this if (or statement

=IF(OR(TBLtask[state]="Open", TBLtask[state]="Pending", TBLtask[state]="Work in Progress", TBLtask[state]="Closed Complete"),COUNTIFS(TBLtask[opened_at], ">="&$E$4, TBLtask[opened_at], "<="&$G$4,TBLtask[Acora_Logged], "TRUE"),0)

It works in that it doesn't error BUT it doesn't work in that it is not differentiating other states "Closed Incomplete" and is therefore including that in the final figure....whats wrong with my syntax?

[AliGW]

Explain in WORDS what you think that your formula is doing. It would be helpful to provide a workbook.

The "TRUE" part of the COUNTIFS is definitely wrong, but until I know what you are expecting, I can't suggest an alternative.

[Pjcan1]

Thanks AliGW,

The 'TRUE' part is a field in the table whereby it indicates as to whether the record was written by my team or not. So if it was written by the BangKok team this field will be FALSE in the table, i've now amended it to Yes or No, so the formulae is

=IF(OR(TBLtask[state]="Open", TBLtask[state]="Pending", TBLtask[state]="Work in Progress", TBLtask[state]="Closed Complete"),COUNTIFS(TBLtask[opened_at], ">="&$E$4, TBLtask[opened_at], "<="&$G$4,TBLtask[Acora_Logged], "Yes"),0)

I'm expecting it to get a single number which is the total count of all records that are in one of those states, logged between the dates and where Acora Logged was yes

[AliGW]

Sorry - you aren't telling me what you are trying to do, only what you expect the result to be, and that's not enough for me.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Pjcan1]

Data example attached now, hopefully that makes it clearer?

[AliGW]

Unfortunately, that doesn't fully help.

What do E4 and G4 refer to in your original formula, because there is nothing in these cells in the sample.

You still have not explained in WORDS what you want the formula to do. You have still only given us the results you want, and without an explanation, these mean very little.

[Pjcan1]

Ok so E4 and G4 are in date fields, in this example e4$=01/02/21 and G4$=28/02/21.

So i want the formulae to check each line and if the state is equal to one of those, then i want to count the instances which are between the date range (e4 to g4) and where they were logged by that team

[ben8563]

Please Login or Register  to view this content.