I have resolved how to add Numbers in a given column for 2 or more linked work sheets.

EXAMPLE; =SUM(SHEET1!F:F,SHEET2!F:F)

I can also add NAMES or TITLES in a given column for one work sheet.

EXAMPLE; =COUNTIF( SHEET 1!L:L, “WEST MIDLANDS”)

How can I add NAMES or TITLES in a given column for 2 or more linked work sheets?????

✔ Best Answer

Does the text string "Walsh" appear by itself in the cell or is it a part of a longer string? Either way try this, with your data like:

E 1) Tom Walsh 2) Welsh 3) Walsh & Sons 4) Wash 5) Walsh 6) Wish 7) Waste 8) Walsh 9) Welsh 10) WishEnter this formula is any cell:

=COUNTIF(E1:E10,"*walsh*")

If you have more then ten rows, simply modify the range E1:E10 to suit your needs.

The Star or Asterisk character on either side of the string "Walsh" are

wild card figures and match "any character any number of times".You should get 4 as an answer.

Edit Added:

In your other posts you requested a formula to be used across multiple sheets.

Although you did not mention it in this post, in case that is what you

need then try something like:=COUNTIF(SHEET1!E1:E10,"*walsh*")+COUNTIF(SHEET2!E1:E10,"*walsh*")+COUNTIF(SHEET3!E1:E10,"*walsh*")

There are other ways of doing this, but this is the simplest.

MIKE

message edited by mmcconaghy

What version of Excel are you using, if you have 2007 or 2010, then

use the =COUNTIFS() function, note the trailing S in the name.=COUNTIFS( SHEET1!L:L, “WEST MIDLANDS”, SHEET2!F:F, "EAST MIDLANDS")

If you are using 2003 or older then you will need a =SUMPRODUCT() function:

=SUMPRODUCT(--(SHEET1!L1:L200="WEST MIDLANDS"),--(SHEET2!F1:F200="EAST MIDLANDS"))

NOTE: using the =SUMPRODUCT() you

use the whole column reference like F:F or L:L, you can only use the actual range.CAN NOT

Which is not a bad idea, even for the =COUNTIFS() function.

Do you really want to calculate 1,048,576 rows in 2007

or 65,536 in 2003....Also, in the future, Excel questions are best asked in the Office Software forum.

ADDED Note:

Just so you know, in 2007 there is also a =SUMIFS() function, again, note the trailing S.

See here:

https://support.office.com/en-us/ar...

MIKE

message edited by mmcconaghy

Mike. thank you for your help. I will set too and try this ASAP.

Regards Walshy.

Just a little tip for the future. If you have any more Excel questions put them on the Office Software Forum. You did OK this time but in general that is likely to be the first place Excel helpers go to. EDIT: Clarified specific forum.

Alwayspop back and let us know the outcome - thanks

message edited by Derek

Mike i copied your solution faithfully but found it did not work!

just to re clarify i am trying to add all the "Walsh" in column E, on sheet 1 & 2 & 3 etc.

Not sure I understand what your trying to do. Are you trying to count the number of occurrences, of the text string Walsh,

that appear in Column E?Or is Walsh a column Header and your trying to sum the values in any column

that has Walsh as the Header?If you can post a small sample of your spreadsheet, after reading this How-To which explains the use of the < PRE > tags, and please use Column Letters and Row numbers for clarity.

http://www.computing.net/howtos/sho...

EDIT ADDED:

What version of Excel are you using?

MIKE

message edited by mmcconaghy

I am trying to count the number of occurrences, of the text string Walsh,

that appear in Column E

i am using excel 2007

Does the text string "Walsh" appear by itself in the cell or is it a part of a longer string? Either way try this, with your data like:

E 1) Tom Walsh 2) Welsh 3) Walsh & Sons 4) Wash 5) Walsh 6) Wish 7) Waste 8) Walsh 9) Welsh 10) WishEnter this formula is any cell:

=COUNTIF(E1:E10,"*walsh*")

If you have more then ten rows, simply modify the range E1:E10 to suit your needs.

The Star or Asterisk character on either side of the string "Walsh" are

wild card figures and match "any character any number of times".You should get 4 as an answer.

Edit Added:

In your other posts you requested a formula to be used across multiple sheets.

Although you did not mention it in this post, in case that is what you

need then try something like:=COUNTIF(SHEET1!E1:E10,"*walsh*")+COUNTIF(SHEET2!E1:E10,"*walsh*")+COUNTIF(SHEET3!E1:E10,"*walsh*")

There are other ways of doing this, but this is the simplest.

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll