SUMIFS and COUNTIFS Question with Yes and No as One Criteria

[QBERT]

Hello.

I have numerous large data sets each one on its own worksheet labeled by report date with numerous columns. I need to create a simple summary table that involves a few columns from each individual worksheet. The good news is that I only deal with one report at a time so i only deal with one worksheet at a time as follows:

Data Set - Worksheet is labeled as DEC_4_2016

Column A: Error Type (10 Different Choices) -
Column B: Error Fixed (Yes or No) - This column lets me know if the error has been fixed
Column C- Q; Other Identifying Data - Other Factors Not Needed for this question

My Summary Table that I need to create is as follows:

Column A: Error Type (Lists all 10 possible errors)
Column B Header: Report Date (DEC 4 2016)
Column C Header: Report Date (Dec 17 2016)
Column D - Z Header: Report Date X....

The end result table should show me all possible error types (i.e. 10 different error types in Column A and the corresponding errors that have been fixed ("Yes") by report dates (Row B-Z).

Thanks,

Qbert

 

[etaf]

you should be able to use
=COUNTIFS(INDIRECT(B$1&"!A:A"),A2,INDIRECT(B$1&"!B:B"),"yes")

this will use the sheetname in the header in B and use indirect to apply that sheet name to the formula
INDIRECT(B$1&"!A:A")
so this will look at column A on sheet dec_4_2016
and then use
INDIRECT(B$1&"!B:B"),"yes"
dec_4_2016 to find YES in column B

now you can copy the formula down and it will use column A for all the different types
if you copy across - it will pickup the different sheets

 

[etaf]

Meant to add a $ to
=COUNTIFS(INDIRECT(B$1&"!A:A"),$A2,INDIRECT(B$1&"!B:B"),"yes")

example mock up here
https://www.dropbox.com/s/o3jnvxqfymsuzlu/Sum_Sheets.xlsx?dl=0

 

[QBERT]

Meant to add a $ to
=COUNTIFS(INDIRECT(B$1&"!A:A"),$A2,INDIRECT(B$1&"!B:B"),"yes")

example mock up here
https://www.dropbox.com/s/o3jnvxqfymsuzlu/Sum_Sheets.xlsx?dl=0

This works perfectly. Thank you!

Qbert

 

[etaf]

you are welcome