Solved I desire help with a formula

November 27, 2014 at 05:58:49
Specs: Windows Vista
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?????

See More: I desire help with a formula

Report •


✔ Best Answer
November 30, 2014 at 14:57:04
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) Wish

Enter 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

http://www.skeptic.com/

message edited by mmcconaghy



#1
November 27, 2014 at 07:52:24
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 CAN NOT use the whole column reference like F:F or L:L, you can only use the actual range.
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
November 27, 2014 at 09:44:56
Mike. thank you for your help. I will set too and try this ASAP.
Regards Walshy.

Report •

#3
November 27, 2014 at 13:13:27
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.

Always pop back and let us know the outcome - thanks

message edited by Derek


Report •

Related Solutions

#4
November 30, 2014 at 07:08:37
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.

Report •

#5
November 30, 2014 at 08:54:42
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
November 30, 2014 at 14:11:02
I am trying to count the number of occurrences, of the text string Walsh,
that appear in Column E
i am using excel 2007

Report •

#7
November 30, 2014 at 14:57:04
✔ 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) Wish

Enter 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Ask Question