SUMIF, NOT Function, excluding specific text in return

Glen Urquhart

Hi All,

Hoping someone may be able to help with the final section of the below formula.

it works as it should up unit the last bit.

Aim of the formula:

Sum of "ScheduleValue"

if:

ScheduleWCDeliveryDate is more than 01/12/2020 (Works)

ScheduleWCDeliveryDate is less than 30/12/2020 (Works)

ScheduledDespatchDate is Blank (Works)

ScheduledStatus does not Equal "Cancelled" (Invalid Op)

This is what I have, which worked, until entering the last section.

Any advice on how to edit would be greatly appreciated, I'm unfamiliar with the NOT function.

I tried combining with CONTAINS but similar result!

=SUMIFS({ScheduleValue}, {ScheduleWCDeliveryDate}, >=DATE(2020, 12, 1), {ScheduleWCDeliveryDate}, <=DATE(2020, 12, 30), {ScheduleDespatchDate}, "", {ScheduleStatus}, NOT("CANCELLED"))

Thanks in advanced,

Glen

Mike Wilday

Try replacing your Not statement with something like this....

NOT(@ cell = "Cancelled")

=SUMIFS({ScheduleValue}, {ScheduleWCDeliveryDate}, >=DATE(2020, 12, 1), {ScheduleWCDeliveryDate}, <=DATE(2020, 12, 30), {ScheduleDespatchDate}, "", {ScheduleStatus}, NOT(@cell = "Cancelled"))

Mike Wilday

Try replacing your Not statement with something like this....

NOT(@ cell = "Cancelled")

=SUMIFS({ScheduleValue}, {ScheduleWCDeliveryDate}, >=DATE(2020, 12, 1), {ScheduleWCDeliveryDate}, <=DATE(2020, 12, 30), {ScheduleDespatchDate}, "", {ScheduleStatus}, NOT(@cell = "Cancelled"))

Glen Urquhart

Wonderful, thank you so much Mike, worked a charm!

Mike Wilday

Awesome! Glad I could help out.