Extended formula using Indirect

soulexistence

New Member
Joined
Mar 20, 2019
Messages
2
The original formula looks like this:
<code>
=SUM(COUNTIFS(Details!$BA:$BA,"A",Details!$AZ:$AZ,">"&"2018-12-31",Details!$AZ:$AZ,"<"&"2019-4-1")+(COUNTIFS(Details!$BA:$BA,"N",Details!$AZ:$AZ,">"&"2018-12-31",Details!$AZ:$AZ,"<"&"2019-4-1")))
</code>
The purpose of this is to feed into charts from data on the Details page, counting A's and N's, which they use to mean complete. (I don't design the trackers, just try do the charts)
Because the client may ask for different columns to be included in the count I want this to be more dynamic. I have made the column headers dropdowns based on the Details page, and have a selector for what quarter the data will be based on. This example is Q1.

This is the formula I attempted to write to do all of this:
<code>
=SUM(COUNTIFS(INDIRECT("'Details'!"&B9:$B9),"A", INDIRECT("'Details'!"&$B8:$B8),">"& INDIRECT("INDEX('BuildData'!C4:C7,MATCH('Charts'!R7,'BuildData'!B4:B7))"), INDIRECT("'Details'!"&$B8:$B8),"<"& INDIRECT("INDEX('BuildData'!D4:D7,MATCH('Charts'!R7,BuildData!B4:B7)")) +COUNTIFS(INDIRECT("'Details'!"&$B9:$B9),"N", INDIRECT("'Details'!"&$B8:$B8),">"& INDIRECT("INDEX('BuildData'!C4:C7,MATCH('Charts'!R7,'BuildData'!B4:B7))"), INDIRECT("'Details'!"&$B8:$B8),"<"& INDIRECT("INDEX(BuildData!D4:D7,MATCH(Charts!R7,BuildData!B4:B7))")))
</code>
But the sticking points are with the "<" and the dates, because, as you can see in the original, they look like this: "<"&"2018-12-31"

So my question is how do i get the indirect to do that? I've tried "<" & char(34) & indirect...,
I've tried "<" & """ & Indirect..., I've tried moving the """ and &char(34) & around inside and outside Indirect to get it to work, but it doesn't.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hi,

Try the following function:

Code:
=SUM(COUNTIFS(INDIRECT("'Details'!"&B9:$B9),"A",INDIRECT("'Details'!"&$B8:$B8),[COLOR=#ff0000]">"&INDEX(BuildData!C4:C7,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR],INDIRECT("'Details'!"&$B8:$B8),[COLOR=#0000ff]"<"&INDEX(BuildData!D4:D6,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR])+COUNTIFS(INDIRECT("'Details'!"&$B9:$B9),"N",INDIRECT("'Details'!"&$B8:$B8),[COLOR=#ff0000]">"&INDEX(BuildData!C4:C7,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR],INDIRECT("'Details'!"&$B8:$B8),[COLOR=#0000ff]"<"&INDEX(BuildData!D4:D7,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR]))

As you can see, I removed INDIRECT function from your dates parameters. INDEX will already return the value for a given intersection of cells, so there is no need for additional INDIRECT.

You can test these two functions separately and see if you get the correct results:
Code:
=[COLOR=#ff0000]">"&INDEX(BuildData!C4:C7,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR]
[COLOR=#0000ff]="<"&INDEX(BuildData!D4:D6,MATCH(Charts!R7,BuildData!B4:B7))[/COLOR]

The first one should give you: >43465 and the second one: <43556.

Hope it helps.
 

soulexistence

New Member
Joined
Mar 20, 2019
Messages
2
Thank you, your help pointed me in the right direction. You were right, I didn't need to use the indirect for the date, i just used the cells where the date was being held. I actually found a couple other places where my code was overly complicated, and i got it working.
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
:) glad to hear it! Take care.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,281
Members
430,536
Latest member
Manoj Gaidhankar

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