Post Text if = 0 but Count if not 0 Forumla

[RAVENSP]

Hi

For the life of me i cant work this out

I want to have it count certain cells if it has the Word "Not Bought" but if it has no cells with "Not Bought"
it inputs "0" which is correct but I would like instead of "0" it puts the word "Complete"

here is what i tried

=COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"),"Complete")

So it matches up all not bought for week 18 but it puts 0 and love to replace that with word "Complete"
but also count "not bought" if that week 18 had say 2 not boughts it woud put in number "2"

Thank you

 

[DushiPunda]

Okay, I think I'm following now. How's this:
=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",SUM(COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"),COUNTIFS(Via!A2553:A19999,"Oversale",Via!AN2553:AN19999,"18")))

Or, maybe this:
=IF(SUM(COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"),COUNTIFS(Via!A2553:A19999,"Oversale",Via!AN2553:AN19999,"18"))=0,"Complete",SUM(COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"),COUNTIFS(Via!A2553:A19999,"Oversale",Via!AN2553:AN19999,"18")))

 

[Aladin Akyurek]

Ok thats great as works if it has the Word Not Bought but I also need it to work if it has the word "Oversold"

is that possible to have 2 words as the trigger?

thank you

1. Either...

=SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversold"},Via!AN2553:AN19999,18))

Custom format is as before:

[=0]"Complete";General

2. Or...

=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversold"},Via!AN2553:AN19999,18)),SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversold"},Via!AN2553:AN19999,18)),"Complete")

I'd advise the first option for reasons of efficiency and compactness.

 

[RAVENSP]

Hi

I used at the end
=SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversold"},Via!AN2553:AN19999,18))

Custom format is as before:

[=0]"Complete";General

but i must remember to do the custom format as already forgot

Thank you so much for all your help Aladin Akyurek and DushiPunda

Great great stuff