Countifs on multiple conditions across multiple sheets

[Sirod]

Hello,

I have a workbook with multiple worksheets, one for each month. Each sheet has names in column A and a status (open or closed) in column B. People are listed more than once on each sheet. The people could be listed on multiple sheets (for each month) across the workbook. I need to create a summary table that will count the number of open cases each person has across all of the sheets in the workbook.

Each sheet is set up as follows:

Name Status
Dave Open
Dave Closed
John Closed
Ann Closed
John Open
John Open
Ann Open
Ann Closed
Dave Closed

Thanks.

 

[Saba Sabaratnam]

Hi

You could use indirect function to count numbers from various worksheets.

My workbook has the following layout.

Jan worksheet has the following data.

Feb worksheet has the following data.

Enter the following formula in cell B4 and copy it down and accross

=COUNTIFS(INDIRECT(B$3&"!"&"$A:$A"),Sheet1!$A4,INDIRECT(B$3&"!"&"$b:$b"),Sheet1!$A$1)

For other months, enter worksheet name and extend the formula. Then add all months to get total numbers by each name.


Kind regards

Saba

 

[Sirod]

Hi

You could use indirect function to count numbers from various worksheets.

My workbook has the following layout.


View attachment 30573

Jan worksheet has the following data.

View attachment 30574

Feb worksheet has the following data.

View attachment 30575

Enter the following formula in cell B4 and copy it down and accross

=COUNTIFS(INDIRECT(B$3&"!"&"$A:$A"),Sheet1!$A4,INDIRECT(B$3&"!"&"$b:$b"),Sheet1!$A$1)

View attachment 30576

For other months, enter worksheet name and extend the formula. Then add all months to get total numbers by each name.


Kind regards

Saba

Thanks Saba. How can I tweak this formula so that it totals all of the sheets? I only want to report back one number for each person.

 

[Saba Sabaratnam]

You could use a sum formula as given below and report the number.

Formula in F4 is:

=SUM(B4:E4)

Kind regards

Saba