Search code examples
excelworksheetconsolidation

Manipulate Data Across Multiple Sheets

I am trying to use a simple formula across multiple worksheets (within the same workbook) to effectively consolidate data.

Current Formula: =COUNTIF('5-01-20:5-29-20'!A:A, "Laptop")

Basically, look at all of the cells in the 'A' columns on sheet named '5-01-20' through sheet named '5-29-20' and count the cell if it contains the word 'Laptop'

This formula returns a #Value error, as does the same formula using a named range. A quick Google search suggests that the CountIF function cannot use this type of range. Is there another solution I can try that isn't combining all of the data onto a single worksheet?

I have also tried a PIVOT Table, but was unable to get it to contain more than one Table/Worksheet.

Solution

  • You will need to create a list of the worksheets then refer to that list using INDIRECT() and wrap the whole in SUMPRODUCT. So for example if your list of sheet names is in Z1:Z29:

    =SuMPRODUCT(COUNTIFS(INDIRECT("'"&Z1:Z29&"'!A:A"),"Laptop"))