Excel help please

Soldato
Joined
17 Dec 2003
Posts
3,247
Location
Peterborough
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 :D
 
Associate
Joined
7 Oct 2013
Posts
1,302
Apologies if I've interpreted wrongly, but you want count how many Blue 0.5m (and so on) across multiple sheets?

I would create a simple "key", in a column, on each sheet where you have your colour and size columns. Where the key is =G1&"-"&H1 , which gives "Yellow-0.5m" (for example)

On your summary sheet, put every combo of colour and cable, and use the same "key". Then just use countif:

B7VK7fD.png

Hope that helps :)
 
Soldato
OP
Joined
17 Dec 2003
Posts
3,247
Location
Peterborough
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 :D

https://imgur.com/a/jnYtVI1
 
Soldato
Joined
1 Apr 2014
Posts
18,540
Location
Aberdeen
Instead of doing it all at once, how about splitting it into stages? Have an intermediate sheet which does the counting for all colours (rows) and all lengths (columns), then pick the value you need?

Here's a macro someone created for me to pick out the result:

Code:
Option Explicit

Public Function ARRAYINDEX(indexRange As Range, columnValue As Variant, rowValue As Variant) As Variant

   Dim row_num As Long
   Dim column_num As Long
  
   With indexRange.Cells(1, 1)
  
      row_num = WorksheetFunction.Match(rowValue, .EntireColumn, 0) - .Row + 1
      column_num = WorksheetFunction.Match(columnValue, .EntireRow, 0) - .Column + 1
     
      ARRAYINDEX = WorksheetFunction.INDEX(indexRange, row_num, column_num)
  
   End With

End Function
 
Soldato
OP
Joined
17 Dec 2003
Posts
3,247
Location
Peterborough
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&"*"))
 
Back
Top Bottom