Extended formula using Indirect

[soulexistence]

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.

 

[JustynaMK]

Hi,

Try the following function:

=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:

=[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]

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]

glad to hear it! Take care.