# SUM/COUNTIF array, looking for INDIRECT method across tabs?

#### aemmett

##### New Member
Here is the formula I am using on the worksheet I currently have all my data stored on.

=SUM((K3:K5999=K6)*(N3:N5999>VALUE(1/1/1990)))

The information I am working with are referral programs and I want to see which referral programs also have a 'start' date. I would like to summarize all this data on a separate tab by Number of referral sources who have a start date.

The problem I am having is when I try to enter this formula from the summary tab. I have heard of using INDIRECT to solve this problem but have failed many times. When I enter the above formula (with coressponding names of the first tab with all my data) it gives me 'VALUE' errors.

Can anyone help me?

THANKS!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Domenic

##### MrExcel MVP
The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. To incorporate INDIRECT into the formula, assuming that A2 contains the sheet name, try the following that needs to be confirmed with just ENTER...

=SUMPRODUCT(--(INDIRECT("'"&A2&"'!K3:K5999")=K6),--(INDIRECT("'"&A2&"'!N3:N5999")>"1/1/1990"+0))

Hope this helps!

#### aemmett

##### New Member
thank you so much for your help! Forgive me, I am very new to excel. I am still having problems with the formula.
Here is the new formula:

=SUMPRODUCT(--(INDIRECT("'"&Child focused outcome&"'!K3:K5999")=K6),--(INDIRECT("'"&Child focused outcome&"'!N3:N5999")>"1/1/1990"+0))

By the way my data looks like this

K3=program --- N3= open date
program1 ---- 1/1/2008
Program1 ----
Program2 ---- 1/2/2008
Program1 ---- 1/3/2008

In a summary tab I would like to count how many program1's have open dates and put this number into a summary tab. The problem I'm having is entering and using this formula across tabs. For the formula listed above I have been getting the #name? error. Any ideas?

#### Domenic

##### MrExcel MVP
Try...

=SUMPRODUCT(--('Child focused outcome'!K3:K5999=K6),--('Child focused outcome'!N3:N5999>"1/1/1990"+0))

If you want the sheet name to be a variable, let A2 contain the sheet name and try the following instead...

=SUMPRODUCT(--(INDIRECT("'"&A2&"'!K3:K5999")=K6),--(INDIRECT("'"&A2&"'!N3:N5999")>"1/1/1990"+0))

Hope this helps!

Replies
2
Views
335
Replies
6
Views
525
Replies
5
Views
148
Replies
1
Views
431
Replies
3
Views
337

1,191,178
Messages
5,985,144
Members
439,942
Latest member
bkexcel11230

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