Add Dynamic Reference in Formula

A Rose

Hi,

I'm creating a summary sheet,

I have a few sheets with the same columns, and I want to add COUNTIF formulas to the summary sheet,

I'd like to know if there's any way that the Reference should be a dynamic option, so if a user chooses on the Reference sheet from a dropdown column: Sheet 1, the formulas knows to use reference #1 in a hidden column or sheet summary column?

I tried creating a column with an IF formula, that IF dropdown column is sheet 1, it should say the reference of sheet 1, but it seems like the COUNTIF looks at the IF column instead of actually using the reference to look it up.

it will always return 0, because there are 0 of that match in the IF column...

Thank you!

Mike TV

@A Rose

=IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row)))

A Rose

@Paul Newcome @Andrée Starå

You guys probably bumped into this in the past, both of you helped me a lot in the past...!

Please let me know if you got any solution/workaround for this.

Thank you!

Paul Newcome

Are you able to provide some screenshots for reference?

A Rose

Hi,

I Hope these screenshots help,

Thanks,

Sheet 3.png

Sheet 2.png

Sheet Summary.png

Sum If Formula.png

Count If Formula.png

Sheet 1.png

Paul Newcome

You would have to build the ranges into the nested IF which in turn is built into the COUNTIFS.

=COUNTIFS(IF(Option# = "A", {Range For A}, IF([Option# = "B", {Range For B})), "criteria")

A Rose

Hi @Paul Newcome ,

I got the idea on how to use the formula based on the sheet #, however my formula is much more complexed then what I shared originally, so I'll share with you the real formula (and change the reference names...)

Here's what worked - if it's only sheet 1:

=IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)))

Here's what didn't work (Incorrect Argument Set) when trying to add if Sheet is Sheet 2,

=IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row))))

Thank you for your help! 😊

Mike TV

@A Rose

=IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row)))

A Rose

Thanks so much @Mike TV!

Paul Newcome

Yes. It looks like you just had a misplaced closing parenthesis and didn't close out the first COUNTIFS.

A Rose

Hi @Paul Newcome and @Mike TV,

(*updated)

I want to upgrade this formula a bit... I don't know how to,

I want to add that if one of the Summary date columns are empty it shouldn't count that IF. (Something like if it's blank skip this COUNTIFS...)

I tried to do it if the cell in reference is empty that worked: OR(@cell >= [From Date Entered]#, @cell = ""),

But how do I do if sheet summary is a date or empty...?

Thank you! 😊