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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
654
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
654
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
:) glad to hear it! Take care.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,468
Members
409,883
Latest member
asharris90
Top