Get the count of the first column in multiple sheets and display them in separate sheet

0
votes

I'm dealing with Excel VLOOKUP and COUNTIF that gets the count of number of entries in first column of every sheet and display the results in separate sheet accordingly with the product

Example:

I have 4 sheets in excel (4 sheet related to 4 products say oldnavy/purell/BB/Macys)

Each sheet has multiple columns.

Im looking to get just get the count of number of rows in first column for all 4 sheets and place in fift sheet with product name in each row and count of it

Tried using VLOOKUP and COUNTIF

Any help would be greatly appreciated

2
excelvbavlookupcountif

2 Answers

1
votes

If you're trying to dynamically reference worksheets, you need to use INDIRECT(). If, in Sheet5 you have your worksheet names "oldnavy/purell/BB/Macys" in A1:A4 respectively, then in cell B1 you could put

=COUNTA(INDIRECT("'" & A1 & "'!A:A"))

And drag it down. COUNTA counts all non-blank cells in a range.

0
votes

If I understand correctly, do this:

=COUNTIF(Sheet1!A:A,"<>")

I believe you understand COUNTIF()

Condition "<>" basically means 'not equal to empty/nothing', so 'count all that is not empty'

Hope that helps