Countifs on multiple conditions across multiple sheets

Sirod

New Member
Joined
Aug 6, 2009
Messages
38
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
Hi

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

My workbook has the following layout.


1611715777486.png


Jan worksheet has the following data.

1611715833397.png


Feb worksheet has the following data.

1611715868142.png


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)

1611715943212.png


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

New Member
Joined
Aug 6, 2009
Messages
38
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

Board Regular
Joined
May 26, 2018
Messages
196
You could use a sum formula as given below and report the number.

1611785245967.png


Formula in F4 is:

=SUM(B4:E4)

Kind regards

Saba
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,751
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top