Post Text if = 0 but Count if not 0 Forumla

RAVENSP

New Member
Joined
May 10, 2017
Messages
11
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
=IF(COUNTIFS(Via!A2553:A19999,"Not Bought")=0,"Complete",COUNTIFS(Via!A2553:A19999,"Not Bought"))
 
Last edited:
Upvote 0
=IF(COUNTIFS(Via!A2553:A19999,"Not Bought")=0,"Complete",COUNTIFS(Via!A2553:A19999,"Not Bought"))


Hi thank you for reply but that did not work as i need it to also use Via!AN2553:AN19999,"18"
as that will make it only count not bought for the week number 18

Also is it possible to have 2 criterias for what it finds in A2553:A19999

eg: i added word "oversale" as i forgot it needs to count if it finds Oversale and Not Bought

=IF(COUNTIFS(Via!A2553:A19999,"Not Bought,Oversale")=0,"Complete",COUNTIFS(Via!A2553:A19999,"1"))

thank you
 
Upvote 0
=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"))
 
Upvote 0
=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,"18"))

Hi


That counts the Not bought but not the oversale
also when it =0 it still only shows as 0 and not word "Complete"


So not sure what is missing on it
i think there is something wrong with this past

{"Not Bought","Oversale"}))=0,"Complete",COUNTIFS(Via!A2553:A19999,"Not Bought"

as it has not bought twice in formula?
 
Upvote 0
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

1. Either invoke

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

and custom format the formula cell as

[=0]"Complete";General

Or just invoke...

=IF(COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,18),COUNTIFS(Via!A2553:A19999,"Not Bought",Via!AN2553:AN19999,18),"Complete")
 
Upvote 0
1. Either invoke

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

and custom format the formula cell as

[=0]"Complete";General

Or just invoke...

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


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
 
Upvote 0
=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","18"})))

So if there are no "Not Bought" or "Oversale" within the range
True: enter "Complete"
False: Count the number of "Not Bought" and the number of "18" in the range.

If you want to include the number of "Oversale" in the False:

=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","18","Oversale"})))

Is that what you want?
 
Upvote 0
=IF(SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","Oversale"}))=0,"Complete",SUM(COUNTIFS(Via!A2553:A19999,{"Not Bought","18","Oversale"})))

The 18 is in Column Via!AN2553:AN19999


So here it is i think a bit clear now


in Column Via!A2553:A19999 --- it has Not Bought or Oversale
in Column Via!AN2553:AN19999 -- it has the 18 (week number)


So i need it to count if column A2553:A19999 has Not Bought or Over
and only count for the week 18 that is in Column AN2553:AN19999


and it finds no word "not bought" or "oversale"


it will put as result - Complete
but if it finds those words it will count those words and put in result




 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top