COUNTIF across multiple worksheets

[slbutter1]

Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" etc. that occur in the exact same cell in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query for me but gives the "VALUE" error message when I insert the sheet1:sheetxxx range.
Thank you,
Stephen Butter

 

[all4excel]

Thats great Alladin..

Thanks a lot for the help on an old thread..

 

[Aladin Akyurek]

Thats great Alladin..

Thanks a lot for the help on an old thread..

Thanks, I'm aware of the fact that post #28 is still open...

 

[jws]

Hello,

I have a similar situation and felt I could keep it in the same thread; if I should post in a new one please let me know.

• I have weekly tabs named WEEK1, WEEK2, etc.
• I have created a “SheetList” on an admin tab as noted with all the weeks in a year

On my Summary tab I have a list of locations that go from A2:A300. What I am trying to do is get a running count through all the weeks on how many times a location came up in the top ten list across all weeks.

• For those weekly tabs, the range is A2:A11 where the location would populate
• On the Summary tab, the total location list goes from A2:A300 as noted

This is the formula I have as of now:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A$2:A$11"),A2))

If I copy and paste the formula down, it “should” read?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A$2:A$11"),A300))

As of now, I get a #REF! error, do I have to make this formula an array?

 

[jws]

** I have this solved **

I got ahead of myself and did not make all the tabs yet, but had them included in my sheet list

 

[Fknoll27]

Aladin thank you so much for the info it has helped me so far but now i am stuck and i dont know why I am unable to get the formula to work.

Here is my situation;(please advise i am a beginer)
I have a workbook with about 80 worksheets.
On a new tab I have created a macro to list all of the tab names;

Sub ListSheetNames()
Dim R As Range
Dim WS As Worksheet
Set R = ActiveCell
For Each WS In ThisWorkbook.Worksheets
R.Value = WS.Name
Set R = R(2, 1)
Next WS
End Sub

I highlighted all the cells (A:A) and named them MySheets.
I am using the code;

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!K:K"),B2))

Where the name that i type in B2 will search the MySheets(the entire workbook) in column K.

For whatever reason it is not working.
Also, the tabs are dates in the format 12-12-2013 where as the column of tab names are in the format 12/12/2013. Not sure if this is a factor...

 

[Aladin Akyurek]

Hi,

I'm not sure if any of the formulas that have been posted thus far in this thread is working for me. I'm trying to count text occurrences given certain conditions (e.g. in four columns of data, I would like to count the occurrences of A & B, A & C, and A & D).

This is the formula I am currently using:

=SUM(((A:A=A3)*(B:B=C1)),((A:A=A3)*(K:K=C1)),((A:A=A3)*(L:L=C1)))

This formula will count multiple criteria, but I'm not exactly sure how to go about making it count them accross different worksheets. I'm not too familiar with Excel and I believe the INDIRECT() function will help, but again, I'm not exactly sure how to utilize it with my current function. I'm running Excel 2003.

Anyone's help is greatly appreciated. =)

=SUM(((A:A=A3)*(B:B=C1)),((A:A=A3)*(K:K=C1)),((A:A=A3)*(L:L=C1)))

won't work on the 2003 system.

What do you have in A3?

 

[DizzyCayne]

Hello all,

An old thread I know, but one extra question to add

From cell E16 of a worksheet, I wish to count how many times the number 3, and the number 4 turn up in cell D16 of most worksheets (Defined in Sheetlist).

Currently I have this formula, which works well.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'! D16"),"4") + COUNTIF(INDIRECT("'"&Sheetlist&"'! D16"),"3"))

However, I would also like to the same counting method, initiated from cell E17 for cells D17 of Sheetlist. I wish to continue this for hundreds of rows.

A simple copy and paste doesn't work, presumably because the INDIRECT function locks the cell D16

Short of typing out the formula hundreds of times exchanging D16 for D17,D18...D118, is there a simpler way?

Thanks

 

[Aladin Akyurek]

@DizzyCayne

Looks like you want:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!"&CELL("address",D16)),{3,4}))

 

[DizzyCayne]

@Aladin

Thankyou very much.

I would like to check to see if I understand why this works.

Is it because the CELL function enabled Excel to recognise D16 as a cell reference, and in my example, the D16 wasn't recognised as a cell reference?

DizzyCayne

@DizzyCayne

Looks like you want:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!"&CELL("address",D16)),{3,4}))

 

[Aladin Akyurek]

INDIRECT here resolves a text value into a reference (address).

In

INDIRECT("'"&Sheetlist&"'!D16")

D16 is fed to INDIRECT as a text value (as result of double quotes around it) which stands for an address.

This means the formula is 'fixed' to this address.

Since we want D17, D18, etc. as we copy down the formula, we make use of the CELL function. When its first argument is set to address, CELL returns the address of its second argument, which is then concatenated to sheet names to compose a definite address which INDIRECT can resolve.

Hope this helps.