SumIF Problem

[HangMan]

I'm using this formula to acquire values from another worksheet

=IFERROR(IF(INDEX(INDEX(INDIRECT("'"&$A2&"'!$1:$1000"),COLUMN(A1),),ROW($A$2))="Red",INDEX(INDEX(INDIRECT("'"&$A2&"'!$1:$1000"),COLUMN(A1)+1,),ROW($A$2)),""),"")

My actual sheet is very complex. The formula is strctured in this way to allow me to drag it both across and down incrementing both row numbers and column letters, so it picks up the relevant values in the relevant cells from around 200 individual worksheets. I simply add each worksheet and name the sheet to the the value of A2, which when dragged down becomes the value in A3, A4, A5 etc in my Summary worksheet.

My problem is, that I want to be able to adapt this to become a SUMIF formula, so that rather than just picking up a single value, it sums values across a range, so effectively I want to adapt the above to say

=SUMIF(B1:S1,"Red",B2:S2)

but I need to maintain the same structure, using the "'"&$A2&"'!$1:$1000" to reference each sheet.

I've attached a greatly simplified sample to try an show what I'm trying to achieve. In cell B2 of the Summary sheet I need, using the same structure, to get the SUM of the values in cells B2:S2 on sheet 6BG, if the value in Row 1 = Red. I could of course take the oirginal formula and simply repeat the formula by adding multiples of it to add the value from B2, C2, D2 etc, but my actual sheet has around 150 columns, which is why I want to use a SUMIF solution. (I'm currently iunable to attach anything as there seems to bre problems with attachments at the moment)

Is there any way to adapt =IFERROR(IF(INDEX(INDEX(INDIRECT("'"&$A2&"'!$1:$1000"),COLUMN(A1),),ROW($A$2))="Red",INDEX(INDEX(INDIRECT("'"&$A2&"'!$1:$1000"),COLUMN(A1)+1,),ROW($A$2)),""),"") from an 'IF' to a 'SUMIF', I've tried and so far failed.

Many thanks

[ratcat]

G'day

=SUMIF(B1:S1,"Red",B2:S2)

won't work but...

=COUNTIF(B1:S1,"Red")

will

You cannot sum words but you can count them.