[Misc] Excel Question...

[Eeyore]

After trying for ages I have finally come to the all knowing place.

I am trying to count the number of sells in a row minus those that contain certain words.

If it was a single word such as Apple it would be:

=COUNTIF(E9:AH9,"<>*Apple*")

It then gives me number of cells that do not contain the word 'Apple'

But...

But if I want it to give me the number of cells in the column that do not contain the words 'Apple' 'Banana' and 'Orange' (which would therefore be more) how do I add to the formula ?

Yours in desperation.

 

[Algernon]

CLICK ------Watch this

From 2:20 ish on, excluding multiple items is covered.

 

[Seagull27]

After trying for ages I have finally come to the all knowing place.

I am trying to count the number of sells in a row minus those that contain certain words.

If it was a single word such as Apple it would be:

=COUNTIF(E9:AH9,"<>*Apple*")

It then gives me number of cells that do not contain the word 'Apple'

But...

But if I want it to give me the number of cells in the column that do not contain the words 'Apple' 'Banana' and 'Orange' (which would therefore be more) how do I add to the formula ?

Yours in desperation.

Haven't got a spreadsheet to hand to test this, but I think you just need COUNTIFS? (Note the S on the end)

=COUNTIFS(E9:AH9,"<>*Apple*",E9:AH9,"<>*Banana*",E9:AH9,"<>*Orange*")

 

[AstroSloth]

After trying for ages I have finally come to the all knowing place.

I am trying to count the number of sells in a row minus those that contain certain words.

If it was a single word such as Apple it would be:

=COUNTIF(E9:AH9,"<>*Apple*")

It then gives me number of cells that do not contain the word 'Apple'

But...

But if I want it to give me the number of cells in the column that do not contain the words 'Apple' 'Banana' and 'Orange' (which would therefore be more) how do I add to the formula ?

Yours in desperation.

I'd imagine you could use the highlight cells rule function to highlight cells, then count the number of cells that aren't highlighted.

 

[mwrpoole]

Haven't got a spreadsheet to hand to test this, but I think you just need COUNTIFS? (Note the S on the end)

=COUNTIFS(E9:AH9,"<>*Apple*",E9:AH9,"<>*Banana*",E9:AH9,"<>*Orange*")

This is the correct solution. COUNTIFS allows multiple criteria to be used whereas COUNTIF is for single criteria.

 

[Eeyore]

NSC, you're beautiful.

Thank you so much. These simple formulas will save hours of work for the project I am doing at present.

 

[tronnogull]

Chat gpt is also great at answering questions like this. You need to be careful to phrase the question un ambiguously.