Excel help please

[mad_allan]

Hi all,

I have a spreadsheet with different names on each sheet and multiple columns. The only 2 columns I am interested for the formula are G and H.

Column G is a list of colours (Yellow, Blue, Grey Etc...
Column H is a size (0.5m, 1m, 1.5m, etc...)

On a separate sheet, I have added the sheets I want to search in A, Criteria in B and Total in C

Using =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A23&"'!G1:G55"),B2)) tells me the number of cables needed for each colour and this works OK.

I now need to know what size of each colour, e.g Blue 0.5m would be 8, Blue 1m would be 4.

I have tried so many formulas and none are working, latest on I tried was:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A2:A23&"'!G1:G55"),B2,INDIRECT("'"&A2:A23&"'!H1:H55"),F2))

G1-G55 is the colour column to search
H1-H55 is the size column to search
B2 is Blue
F2 is 0.5m

Any help greatly appreciated before I scream

 

[mad_allan]

Hi Lacky,

I have 78 sheets, hence the Indirect that lets me reference all sheets or the formula will be huge.
There are 12 colours but lengths increment in 0.5m all the way to 112m. That would take all day to try and change it

I must be missing something simple and having a blonde day. CountIFS should work for multiple cells.

Top picture are the 2 columns, bottom is the summary sheet. In columns E/F - there will be 224 variations per colour

https://imgur.com/a/jnYtVI1

 

[mad_allan]

Finally figured it out Just needed a wild card for the number column, if it was text no wildcard needed.

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A2:A23&"'!G:G"),B2,INDIRECT("'"&A2:A23&"'!H:H"),"*"&F2&"*"))