CODEWITHSUNDEEP
excelexcel-formulacountifexcel-indirect
tomm
tomm

Reputation: 23

Dynamic COUNTIFS - No INDIRECT

I have a COUNTIFS statement that dynamically gets the row to count in, which works fine, but I have a small problem with it that I'd like to see if there was a way to fix:

=COUNTIFS(locplan_week,AU$20, INDIRECT(CONCATENATE("Tracker!G",$C56,":UK",$C56)), "B")

$C56 refers to a cell where I have found the row number via a MATCH.

As I said works fine, until someone inserts a column into the Tracker sheet. So am looking for a way to do this without INDIRECT, and where Excel would then take care of adjusting the Column indicator for me.

Upvotes: 2

Views: 281

Answers (1)

user4039065
user4039065

Reputation:

Use INDEX to replace your INDIRECT.

=COUNTIFS(locplan_week, AU$20, INDEX('Tracker'!G:UK, $C56, 0), "B")

I'm assuming that the orientation and size of locplan_week and a single row within 'Tracker'!G:UK is the same.

Upvotes: 2

Related Questions