Formula to count text/word in multiple sheets

Hi all,

I have a formula that calculates the amount of times the word appears on a sheet but now my problem is to include different sheets to get the total. My Sheets are renamed.

Basically i have a main data sheet and the rest of the sheets are various names with data on but i need to see how many times that word/text is used. Not sure if i have to name the sheets also to show in the Name Box?

My formula i have is as follows:

=COUNTIF(A1:A1000,A2)

I need to include say for example the following sheet names so it adds it all together :

Week Days
Month
Days

I have a formula I found on excel-easy.com but it give me an error:

=COUNTIF(Days!A1:A1000,D2)+COUNTIF(Month!A2:A6,D2)+COUNTIF(Week Days!A2:A6,D2)

Thanks in advance for any help

You need to put ' around sheet names if contain blanks:

Formula:

Please Login or Register  to view this content.

Hi zbor,

Thank you for the reply and suggestion, i changed it to your formula but now i am getting 0 as result.
Maybe i am doing the criteria wrong.

I tried to load the excel sheet but does not load so i attached images for you to understand better.


Sheet 1 tab.JPG SHEET 1

Sheet 2 tab.JPG SHEET 2

Sheet 3 tab.JPG SHEET 3

Data tab.JPG DATA SHEET


Kind Regards

You are not covering proper ranges.

Write this in B1 of DATA SHEET and extend down:

Formula:

Please Login or Register  to view this content.

It is only giving me a 0 in B1 for all the rows down.

If i put this formula in on the data sheet =COUNTIF(A1:A1000,A1) and extended down I get the answer 1 which is correct as there is only one listed of each word.

But when i try to add all the other sheets like the previous formula it does give me a result, well i get 0 but there the words are on the other sheets.

sorry for being a pain

Regards

Formula:

Please Login or Register  to view this content.

Wait, what?

=COUNTIF(A1:A1000,A1) is looking in THAT sheet. Not other sheets.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.
Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I was just showing that formula calculates what i need but when i need to
incorporate the additional sheets it does not give me a total.

Regards

So for word Excel formula:

Formula:

Please Login or Register  to view this content.

returns 24.

What is your expected result?

Hi,

Thanks that works exactly the way i want but somehow when i incorporate that into my original it does not calculate the words, it returns 0 for all the rows.

I have altered the original just for you to see, i'm sure it has to do with the sheet names,

your help is appreciated.

@JohnTopley you're a *

That will be my next big thing as recipes have all different ways of wording of each spice.

Thank you for your help, much appreciated...


Also thank you to zbor for helping out

@JohnTopley

is there a way to change the formula to incorporate multiple sheets with the formula you game me.
Just that the formula is repetitive for the multiple sheets.
say if you have 20 different sheets to repeat the same formula (--ISNUMBER(SEARCH(A2,Curry!$A$1:$C$1000)))+SUMPRODUCT for each of the sheets it will be come quiet long! As
you can see the formula you sent is for 3 sheets only?

Regards

its giving me a #name error now, *crying in palm of hand*

is it "'" & "Curry","Chilli","...... with/out ( ) or ""'&

Starting to think this will be much easier in MS Access

Could i use : in the formula rather

eg - Curry:Chilli if so how would i incorporate it, i get confused with all the "'*(

See the attached:

Named range "List2 is highlighted in Yellow

formula in Column D

Hi

@JohnTopley thank you for the feedback, much appreciated. Man you guys have patience of steel

I have another question not sure if i need to make a new thread or if i can do it in this thread?

Regards

If it is related to this topic i.e. Formula to count text/word in multiple sheets add it here.

If it is completely different, start a new thread.

Still same topic

I have a formula to calculate:

Say you have 1/2 tsp sugar right = the formula gives you 0.5 but what i'm not to sure is how to add in the name of item.

eg. 0.5 Sugar and not just the total 0.5

Formula:

Please Login or Register  to view this content.

See Blends & Mixes tab C2 - C9 with formula.

Regards

Don't you require "0.5 CUP sugar" ?? (or "teaspoon")?

if so ...

=IF(TYPE(SEARCH("/",A2)=1, IF(SEARCH("/",A2=2, VALUE("0 "&LEFT(A3,SEARCH("/",A2+1)),VALUE(LEFT(A2SEARCH("/",A2+1))),VALUE(LEFT(A2SEARCH(" ",A2))) &MID(A2,IND(" ",A2,255)

Curious to know why you are using decimal units than fractions!!!

If you are going "Decimal" why not use "Find/Replace" to change "1/2" to "0.5" in all entries?

giving me an error for the above formula.

"you've entered too many arguments for this function."

To answer your question, it's just that at the end i'd like to know a overall quantity of the item - not worried about how many cups, tsp or tbls etc.

Basically i want too see on the data tab the item and quantity thorough out records and all the sheets.
I'm sure there is a much easier way to go about this, as you've noticed i like complex (why, who knows :P)

your suggestions are welcome that's how we learn new things.

Try

=IF(TYPE(SEARCH("/",A2))=1,IF(SEARCH("/",A2)=2,VALUE("0 "&LEFT(A2,SEARCH("/",A2)+1)),VALUE(LEFT(A2,SEARCH("/",A2)+1))),VALUE(LEFT(A2,SEARCH(" ",A2))))&SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2),255),"cup",""),"teaspoon","")

Or with the units

=IF(TYPE(SEARCH("/",A9))=1, IF(SEARCH("/",A9)=2, VALUE("0 "&LEFT(A9,SEARCH("/",A9)+1)),VALUE(LEFT(A9,SEARCH("/",A9)+1))),VALUE(LEFT(A9,SEARCH(" ",A9)))) & MID(A9,FIND(" ",A9),255)

Yeah both work, thanks but it includes the other text as well

eg: 1 teaspoon Himalayan or sea salt(optional but helps absorption of soup) / 1 Himalayan or sea salt(optional but helps absorption of soup)

instead of

1 teaspoon Himalayan or sea salt / 1 Himalayan or sea salt

but i can play around with that number of characters.



one last ?

why is this

Formula:

Please Login or Register  to view this content.

returning 0 result?

You are going to have real problems here extracting the required data as the text is far from consistent.

re SUM: A2 is text not numeric. If you want to SUM the data you will need to extract the numbers.

A better format format for your data would be:

Columns A to C

Description e.g Garlic Powder
Unit: cup, teaspoon
Quantity: 1/2 (0.5)

You will spend a lot of effort "unravelling" data. A general (and good) rule of design is one column per attribute.

Cool beans, makes sense.

I would like to thank you for your help, and patience.

Keep up the good work....

Take Care

If you interested the following will split the text into 3 column (only tested on one sheet)

Quantity
Unit
Description

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ","|",1)," ","|",1),"|",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Hahahahah same thing i was just trying to put together after your previous post about breaking it up per column.

Thanks will try this out and let you know.

Appreciated

Somehow it's only returning the 1st word/num and not three columns unless i'm inputting it wrong!!!

Sorry ... should have explained: you need to drag across the columns and you will get entries in each column.

See attached in "Blends & Mixes" : columns D to F

Hi,

No problem, thanks for the feedback.

Yeah that's also a way of doing it, cool beans

Take it easy and much appreciation.

Regards