# Countifs on multiple conditions across multiple sheets

#### Sirod

##### New Member
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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Saba Sabaratnam

##### Board Regular
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

##### New Member
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
You could use a sum formula as given below and report the number.

Formula in F4 is:

=SUM(B4:E4)

Kind regards

Saba

Replies
4
Views
43
Replies
2
Views
327
Replies
4
Views
151
Replies
2
Views
74
Replies
20
Views
229

1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

### 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.

### Which adblocker are you using?

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

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