CONCATENATE using 2 SUM formulas

[Darren_workforce]

Hello,

I have 3 cells (X2:Z2) that have values in them calculated from formulas.

X2: =SUMPRODUCT((--(B:B="trainer")*(A:A<>"")) (calculated value of 0)
Y2: =SUMPRODUCT((--(B:B="tl")*(A:A<>""))) (calculated value of 1)
Z2: =SUM(B5,B10) (calculated value of 12)
I also have a SUM formula in Y5: =SUM(X2:Z2) (calculated value of 13)

So far so good...However, I am attempting to combine the data into A2 using CONCATENATE and am having a problem. I'd like to display both the Z2 value (as that is the number of phone reps under the manager) and then Y5 (which displays the phone reps under the manager PLUS the non-phone reps under that same manager).

I used =("("&Z2&" Phone, "&Y5&" Total)") (with and without CONCAT at the front) which should display (12 Phone, 13 Total)...however, it either comes back as 0 value or I get the popup "Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command."

I'm confused where the circular reference might be as I'm referencing 2 different cell values in the CONCAT formula. The only thing I can think of is that the 13 is using the 12 but as I'm not doing any calculations within the CONCAT formula, should that be causing an issue since all the math was done previously?

Thank you in advance for any assistance!!

 

[Darren_workforce]

OK not sure what happened but the cells with the CONCAT formula seem to have displayed the correct information. However they were entered into a test cell. When I attempt to copy/paste into the cell I want them in, I am getting the same issues as above.

 

[Fluff]

The formulae in X2 & Y2 are looking at col A, but you have then put a formula in col A that looks at X2 & Y2, hence you get a circular reference.

 

[Darren_workforce]

The formulae in X2 & Y2 are looking at col A, but you have then put a formula in col A that looks at X2 & Y2, hence you get a circular reference.

OK thank you for providing that explanation, Fluff

 

[Fluff]

You're welcome.