What formula do I use?

[livinlavidaloca]

I am stuck, trying to work out the formula for this spreadsheet summary and would really appreciate some help!

• I want to create a formula which will leave a SINGLE tick in H3 if there are any non-empty cells in 'HMN.MBY' (the name of the tab I am extracting data from), G3:G34 (the cells within the sheet where I am extracting data from).​
• Unfortunately, the only valid formula I have found so far replicates the column positioning of the non-empty cells from 'HMB.MBY, G3:G34'. It displays the ticks in order of which cells are non-empty in 'HMN.MBY'.​
• What I actually want to do is have a single tick in H3 if there are any non empty cells in G3:G34 from 'HMN.MBY'.​

Hope that makes sense, but feel free to ask if you need more information.

Thank you in advance!!

 

[Fluff]

Hi & welcome to MrExcel.
You can use counta like

=IF(COUNTA(HMN.MBY!G3:G34)...)

 

[excelhjalp]

If I understand this correctly it should be enough to count all blank cells within the range and subtract it from the count of cells, which is 35 (from G3 to G34)
So try this:

+IF(COUNTBLANK('HMB.MBY, G3:G34')<35;"Check";"")

 

[Fluff]

which is 35 (from G3 to G34)

Something doesn't quite add-up.

 

[excelhjalp]

Something doesn't quite add-up.

Whoops! Haven't had my coffee yet! Meant 32

 

[Peter_SSs]

Could there be formulas returning "" which you might count as "empty" in the column G range?
Possibly this?

=LET(r,'HMN.MBY-515.628'!$G$3:$G$34,IF(ISNA(FILTER(r,r<>"",NA())),"","✓"))

 

[Phuoc]

Or try

=IF(SUMPRODUCT(LEN('HMN.MBY-515.628'!$G$3:$G$34))>0,"✓","")

 

[Peter_SSs]

Or try

=IF(SUMPRODUCT(LEN('HMN.MBY-515.628'!$G$3:$G$34))>0,"✓","")

Using similar concept ..

=IF(LEN(CONCAT('HMN.MBY-515.628'!G3:G34)),"✓","")

 

[Phuoc]

Or

=IF(TEXTJOIN("",1,'HMN.MBY-515.628'!G3:G34)<>"","✓","")

 

[Peter_SSs]

Or

=IF(TEXTJOIN("",1,'HMN.MBY-515.628'!G3:G34)<>"","✓","")

Fair enough
.. but TEXTJOIN specifying no delimiter - might as well just use CONCAT again?

=IF(CONCAT('HMN.MBY-515.628'!G3:G34)="","","✓")