Soldato
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
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