Adding multiple ranges and counting words....

[Iain McBride]

Stupid question one:
Trying to do a formula to add 2 ranges from another workbook. so far I have:
=SUM('S:\Data Collection\Stats\School\[St Johns.xlsx]Register'!$C$17:$AH$17)
But I also need to add C23:AH27 as well as C17:AH17


Stupid question number two
Trying to add the number of words in a column (from a different workbook) - so I am trying to auto-calculate a register for all my schools to show how many students are in each class:

Tried using COUNTIF but returns #Value error...

Sure its so easy - but brain gone to mush - so any help would be fantastic!!!

 

[Yongle]

Stupid question one: But I need to add C23:AH27 as well as C17:AH17

how about...
=SUM('S:\Data Collection\Stats\School\[St Johns.xlsx]Register'!$C$17:$AH$17)+SUM('S:\Data Collection\Stats\School\[St Johns.xlsx]Register'!$C$23:$AH$27)

Stupid question number two

What are you wanting to add?
- the number of words?
- the number of cells containing one word or more
- which column is being counted?
- is the count conditional on the value in another column? If so which one?

 

[Iain McBride]

Thanks Yongle
The cells are column c, rows 3:14
I want to count the number of words in that range.
No conditionality required now.

Cheers

 

[Yongle]

Only you know what is in your cells - make sure that there is nothing "extra" there (numbers etc) to break the logic
- method is to put all the words into one long string , each word separated by spaces
- the word count is the number of spaces + 1

FORMULA LOGIC
To count words in a range of cells
- concatenate all cell values adding a space between each value
- TRIM the result (to eliminate double spaces)
- count the number of characters (= X)
- remove all spaces
- count the number of characters (= Y)
- number of spaces = X-Y
- number of words is ONE more than that

BASIC FORMULA
If the cell is in the same workbook and same sheet as the range, then the formula is
=LEN(TRIM(TEXTJOIN(" ",TRUE,C3:C14)))-LEN(SUBSTITUTE(TRIM(TEXTJOIN(" ",TRUE,C3:C14))," ",""))+1

GET IT INTO ANOTHER WORKBOOK
with this formula
='S:\Folder\subFolder\[OtherFileName.xlsx]Sheet Name'!$A$1


GET THERE IN ONE STEP
with this formula
=LEN(TRIM(TEXTJOIN(" ",TRUE,'S:\Folder\subFolder\[OtherFileName.xlsx]Sheet Name'!$C$3:$C$14)))-LEN(SUBSTITUTE(TRIM(TEXTJOIN(" ",TRUE,'S:\Folder\subFolder\[OtherFileName.xlsx]Sheet Name'!$C$3:$C$14))," ",""))+1

 

[Iain McBride]

Yongle - you're a star - thank you for your help - and your explanation (though I think I will never quiet understand it if i'm 100% truthful!!!)