Counting whether there is text in cells

Iain McBride

New Member
Joined
Aug 17, 2018
Messages
38
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Re: Countingwehether there is text in cells

=SUMPRODUCT((Sheet2!$A$1<>"")*1)+SUMPRODUCT((Sheet3!$A$1<>"")*1)
this formula counts non blank cells in A1 of sheets 2 and 3

<colgroup><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>
 
Upvote 0
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! :LOL:
 
Upvote 0
Re: Countingwehether there is text in cells

a little shorter:

(Sheet2!$A$1<>"")+(Sheet3!$A$1<>"")
 
Upvote 0
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!!
 
Upvote 0
Re: Countingwehether there is text in cells

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


 
Upvote 0
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 :)
 
Upvote 0
Re: Countingwehether there is text in cells

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

 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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