Inserting Cell Text into an Existing Formula

[kazoli]

Hello,

I am struggling with the syntax to combine a COUNTIF function with an INDIRECT function embedded within (or something similiar). Here's my current formula in cell B1:

=IF(A1 = "all",COUNTIFS(Sheet1!Q:Q , Sheet2!M4))
(If A1 in the current sheet = "all", then run a countif to see how many instances of Sheet2!M4 exist within column Q in Sheet1)

What I would like to do is store text in different cells that I could "tack on" to this formula as needed. E.g. Cell A2 contains the text ",Sheet1!R:R, "<>" & "exclude". Then, in my main countifs formula in B1, I'd like to be able to reference Cell A2 in order to add more criteria to the countifs. So, something like =IF(A1 = "all",COUNTIFS(Sheet1!Q:Q , Sheet2!M4 & A2)) would actually be read as =IF(A1 = "all",COUNTIFS(Sheet1!Q:Q , Sheet2!M4 , Sheet1!R:R , "<>" & "exclude"))

I've tried various combinations of using INDIRECT with no luck. Any insight is greatly appreciated - thank you!

 

[Eric W]

Welcome to the MrExcel Board!

INDIRECT (or OFFSET, or INDEX) is used to dynamically change a range reference, not an entire formula. You're asking to change the formula itself. This is not entirely impossible, but it's tricky and depends on your exact needs as to whether it will work. It also requires some variant of VBA.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. In the sheet that opens, paste this code:

Public Function MyCountIfs(ByVal r1 As Range)

    Application.Volatile
    myfunc = "=IF(A1=""all"",COUNTIFS("
    For Each c In r1
        If c <> "" Then myfunc = myfunc & c.Value & ","
    Next c
    Mid(myfunc, Len(myfunc), 1) = ")"
    MyCountIfs = Evaluate(myfunc & ")")
   
End Function

Press Alt-Q to close the editor. Now look at this sheet:

Book1
	A	B
1	all	4
2	Sheet1!Q:Q,Sheet2!M4
3	Sheet1!R:R,"<>exclude"
Sheet5

Cell Formulas
Range	Formula
B1	B1	=MyCountifs(A2:A5)

Voila! There you go. You can add more ranges in A2:A5 and the UDF will add them to the formula it is dynamically creating, then the EVALUATE function in VBA will give you the answer. Not really friendly though. If you use a UDF (User Defined Function) it may be easier to code your actual requirements rather than building a formula.

The EVALUATE function isn't directly available from the worksheet, although there are ways to do it. See here:

Excel Factor 12 Secret EVALUATE Function

This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Suppose you receive a spreadsheet with calculations, but the calculations are stored as text, for display purposes only, and not used to actually derive answers. Without wanting to reinvent the wheel, you decide to...

www.myonlinetraininghub.com

But if you go that route, be aware that it still makes your workbook a macro-enabled workbook. You'd also need the TEXTJOIN function to put the pieces of your COUNTIFS together. You'd need Excel 365 or Excel 2019 for that.

Not the answer you were looking for, but those are your options.