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

aemmett

New Member
Joined
Jun 18, 2008
Messages
2
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? :confused:

THANKS!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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!
 
Upvote 0
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?

Thank you in advance for your help
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top