functions/Formulas

[RH]

I have multiple worksheets and I would like to count the number of times
certain words appear in each worksheet and group them by area. I then want
to count how many times a number appears based on those words.

example: Apple, orange, peas, carrots
I want to count Apples and oranges in all worksheets and then count how many
of them are available.

 

[Jacob Skaria]

Use the below formula to count the number of cells with the text 'Apple' in
Column A of sheets mentioned in the cells E1:E3. You can change the text
'Apple' to a cell reference

=SUMPRODUCT(COUNTIF(INDIRECT("'"&E1:E3&"'!A:A"),"Apple"))

To test this enter the sheet names in cell range E1:E3. Make sure you dont
have any blank cells in this range E1:E3 and all sheet names are exactly same
as the actual sheet names (even spaces count)


'Now to get the sum of (say Quantity) mentioned in ColB of each sheet you
can use the below formula

=SUMPRODUCT(SUMIF(INDIRECT("'"& E1:E3 &"'!A:A"),"apple",INDIRECT("'"& E1:E3
&"'!B:B")))


If this post helps click Yes

 

[RH]

Jacob,
I do not understand how to put the worksheets names in. When I do it gives
me an error of #NAME?.

 

[RH]

Jacob,
Not sure of how to enter sheet names, when I try to put the worksheet names
in, it give me and error of #NAME?

 

[Jacob Skaria]

In cell E1: Sheet1
E2: sheet2
E3: Sheet3



If this post helps click Yes

 

[RH]

I want to count apples and oranges together from all of the worksheets. Can I
go ,"apple","orange"))

 

[Jacob Skaria]

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&E1:E3&"'!A:A"),{"Apple","Orange"}))

If this post helps click Yes