COUNTIF formula made from multiple cells text

[Neocold]

Hello,

I have been trying to get this to work for about 6 hours and I am at the end of my rope.

I am pulling data from another spreadsheet in the workbook and since the locations of the data can sometimes change I am trying to make a COUNTIF formula from values entered on the spreadsheet in the attached photo (yellow cells). One photo shows me trying to get the formula to work by combining the cells to make the formula work and it does not (cell F12). The other photo shows the formula entered directly and working (F22).

I have tried INDIRECT and all kinds of other ways that but I must be missing something???

Thank You for any assistance!

 

[Neocold]

Here is another screenshot showing the formula in cell F12

 

[James006]

Hi,
Have you tried to insert a Pivot Table ?

 

[Neocold]

Hi,
Have you tried to insert a Pivot Table ?

I have not heard of a Pivot Table, I will look into this and see if I can get that to work.

 

[Neocold]

I have not heard of a Pivot Table, I will look into this and see if I can get that to work.

So this seems to be giving me the same problem. I can get the correct data to return based on the pivot table created but I can not figure out how to get the pivot table to use the changing values in the yellow highlighted cells?

 

[Neocold]

What boggles my mind is if I do this on the sheet labeled 'CONFIG'

="'"&B1&""&B12

which properly turns itself into

'SOMS ROSTER'!G691

Then this on the sheet I am trying to get the results on

=INDIRECT(CONFIG!E12)

It properly returns the value (in this case "58")


However, If I do this on the sheet labeled 'CONFIG'

=C1&C12&D1&D12&E1

which properly turns itself into

COUNTIF('SOMS ROSTER'!L620:L690,"Close")

Then this on the sheet I am trying to get the results on

=INDIRECT(CONFIG!F12)

It returns the value of #REF! but if I put in

=COUNTIF('SOMS ROSTER'!L620:L690,"Close")

directly it works?

 

[Neocold]

Found the solution.
=COUNTIF(INDIRECT(CONFIG!F13),"Close")

Thanks to this thread:

COUNTIF picking up data from another tab referenced by a cell

I am finding this difficult to explain, hence why I couldn't find my answer online, so please bear with me. I have a number of tabs in my spreadsheet, call them red, orange, blue, green. I have a summary sheet with the colours in column one and months in B1, C1, D1 etc. I have tried to give an...

www.mrexcel.com