Counting whether there is text in cells

[Iain McBride]

There must be a simple thing that I am missing, but failing miserably!

I am trying to count (on a master spreadsheet) how may responses have happened in Cell B17 on 12 different sheets of a spreadsheet. I have tried count, counta, sumproduct with istext but nothing seems to give me the right answers, does having a drop down element in the cell (yes No option) make any difference.

Thanks
Iain

 

[oldbrewer]

Re: Countingwehether there is text in cells

[TABLE="width: 610"]
<colgroup><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT((Sheet2!$A$1<>"")*1)+SUMPRODUCT((Sheet3!$A$1<>"")*1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]this formula counts non blank cells in A1 of sheets 2 and 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Iain McBride]

Re: Countingwehether there is text in cells

Thanks oldbrewer! Worked a treat - I would like to say I understand how it works but sadly I don't!

 

[Phuoc]

Re: Countingwehether there is text in cells

a little shorter:

(Sheet2!$A$1<>"")+(Sheet3!$A$1<>"")

 

[Iain McBride]

Re: Countingwehether there is text in cells

Thanks for your help - and as you guys are on a roll.....

The same spreadsheets also have two questions

20 Positive result? Yes/No
21 Follow up required? Yes/No

Is it possible to create a formula whereby if 1 or more of the answers is a Yes then it only counts as one in the master spreadsheet?

I know its a confusing query - sorry!!

 

[oldbrewer]

Re: Countingwehether there is text in cells

=if(Sheet2!$A$1="yes"+Sheet3!$A$1="yes">0,1,0) maybe

 

[Iain McBride]

Re: Countingwehether there is text in cells

Thanks oldbrewer, but it throws up #VALUE , will keep playing with it as its miles ahead of where I was with the formula - thanks

 

[oldbrewer]

Re: Countingwehether there is text in cells

=if(or(Sheet2!$A$1="yes",Sheet3!$A$1="yes"),1,0)