Trying to combine 2 AND functions with an OR

BJTidi

Thanks Paul for the help, need to look a little closer but think that is working

BJTidi

I had to add "status"@row<>complete so it doesn't look at the items already completed but I didn't mention that in my question. Looks like we are good to go!

Paul Newcome

@BJTidi Happy to help. 👍️

Carrie V.

@Paul Newcome Been working on this one on the flight from Orlando to Phoenix and can't figure it out. Any help you could give would be great!

This formula is mostly working (still need to test a few more project statuses..).

What is NOT working is when I have the project state as "3_In Planning" and the Start Date = Blank. I want it to return "Check Date" because it doesn't meet the condition of a date less than or more than today. Instead, it says "Started", when the date is blank. If I enter a date greater than today with In Planning, it does correctly return "Not Started".

=IF(OR(AND(ISBLANK([Start Date]@row), [Project State]@row = "1_Request In Review"), AND(ISBLANK([Start Date]@row), [Project State]@row = "2_Backlog")), "OK", IF(OR(AND([Start Date]@row < TODAY(), [Project State]@row = "3_In Planning"), AND([Start Date]@row < TODAY(), [Project State]@row = "4_In Progress"), AND([Start Date]@row < TODAY(), [Project State]@row = "6_Completed")), "Started", IF([Start Date]@row > TODAY(), "Not Started", "Check Date"))))

Thanks,

Carrie

Janna

Hi,

I have a similar problem and hope you can help.

I want to add up all sales to a quarter that fall in April 23, May 23, and June 23 (under a different condition). The formula works very well when only one month is selected for the impact. If I select multiple months, the sales are not counted. 

How can I change the formula so that all values are counted, even if I choose April AND May or April, May AND June? 

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; OR(@cell = "April 23"; @cell = "Mai 23"; @cell = "Juni 23"))

Paul Newcome

@Janna Your formula should be working assuming you have those values present in your range.

Janna

Hi Paul,

thanks for your reply.

The formula works well.

But in the dropdown menu it's allowed to choose more than one value, e.G may AND june. If I do choose more than one value, the formula doesn't work anymore and the fields are not being counted.

I hope you understand what I mean.

Paul Newcome

@Janna My apologies. I didn't realize you were using a multi-select.

Try this instead:

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; OR(HAS(@cell, "April 23"); HAS(@cell, "Mai 23"); HAS(@cell, "Juni 23")))

Janna

I used a semicolon instead of a comma, I think because I am a German user.

That worked perfectly well :) Thanks a lot for your support!

meganellis07

Hi Paul,

I'm hoping you can help me with a similar issue. I'm trying to write a countif formula where the following criteria are met:

task type = project and

status = completed or in progress

I tried to plug in your logic above but am struggling a bit.

Paul Newcome

@meganellis07 Give something like this a try:

=COUNTIFS([Task Type]:[Task Type], @cell = "Project", Status:Status, OR(@cell = "Completed", @cell = "In Progress"))

meganellis07

This was so helpful, thank you Paul!

Paul Newcome

Happy to help. 👍