COUNTIF picking up data from another tab referenced by a cell

[jason7579]

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 example in the table below.

Colours	Jan	Feb
Red	=COUNTIF('Red'!$G:$G,B$1)	=COUNTIF('Red'!$G:$G,C$1)
Orange	=COUNTIF('Orange'!$G:$G,B$1)	=COUNTIF('Orange'!$G:$G,C$1)

I am doing a COUNTIF of column G:G in each tab, and I have the colours listed in column A of my summary tab.
Currently I am creating each formula uniquely on my summary sheet such as the table above, so that I pick up all of the word Jan in column G of the tab Red.
Is it possible to remove the tab name as something I have to change for each row and pick it up from the cell value in column A? e.g. =COUNTIF(A2($G:$G), B$1) - I can't work out how to combine the combination of A2 along with pointing the formula to G:G.

I hope this makes some kind of sense to you?

Thanks in advance.

 

[Fluff]

How about

=COUNTIF(INDIRECT("'"&$A2&"'!$G:$G"),B$1)

Although this will make the formula slow & volatile.

 

[jason7579]

How about

=COUNTIF(INDIRECT("'"&$A2&"'!$G:$G"),B$1)

Although this will make the formula slow & volatile.

Thanks - I really thought there would be a simple, robust formula that I am overlooking as it feels like a feature that should be there without any issues. Although it takes a few seconds longer to create additional lines (I'm just using find and replace so it isn't a huge deal) I would rather have speed and something that works each and every time. For now, unless another method is out there, I will continue as is.

Thanks for taking the time to help.

 

[Fluff]

I really thought there would be a simple, robust formula

There is, it's what I posted.

 

[jason7579]

There is, it's what I posted.

good point, just gave it a go and with the relatively small dataset I'm using it made no tangible difference to speed and usability. So it's definitely my solution to this problem. Thanks for your help.

 

[Fluff]

Glad to help & thanks for the feedback.