Counting (some) words in Excel

[Iain McBride]

Hi, I am trying to create a formula to count worded cells, but to exclude certain words.
the basic chart is:

Column Q
12/10/17
1) yes
2) always
3) DNA
4) Yes all the time
5) Occasionally
6) Only on a Wednesday
7) Absent
8) All the time
9) Each 3rd Month
10) Never

So I want to find I way to find out what responses I have received and to NOT count the "Absent" and "DNA" (Answer should be 8), but struggling between =COUNTIFS, =SUMPRODUCT and also from not knowing what I am doing!!

Any help gratefully received.

Thanks
Iain

 

[Legacy 421546]

I solve almost everything in VBA and this one is easily solved with that tool. If you're ready to go down that route then please elaborate what 12/10/17 means.
Is it a date? Is it relevant?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]always[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]DNA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Yes all the time[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Occasionally[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Only on a Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Absent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]All the time yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Each 3rd Month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Never[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



R2=COUNTIFS(Q1:Q10,"<>"&Q3,Q1:Q10,"<>"&Q7)

 

[Iain McBride]

sorry is the date - and no its not relevant for the formula

 

[Legacy 421546]

ok, does MARZIOTULLIO's formula work?

 

[Iain McBride]

Gone with:
=COUNTIFS(Range,"<>*Absent*",Range,"<>*DNA*",Range,?*") and so far works, now to look at trying to correlate lots of these answers into another spreadsheet!

 

[Legacy 421546]

trying to correlate lots of these answers into another spreadsheet

Then formula should be in the target sheet cell and range should address the source sheet cells

 

[Aladin Akyurek]

Does this help?

=COUNTIFS(Q1:Q10,"?*")-SUM(COUNTIFS(Q1:Q10,{"yes","dna"}))