countifs return values range

[sx200apul]

=SUM(COUNTIFS(D5:D15,"Mountain Blue",A5:A15,"*"&{1,2,3,4,5,6,7,8,9}&"xWindow*")*{1,2,3,4,5,6,7,8,9})

Counting window colours from Accessories Colour column.

this works fine if its 1-9 in the Extras … but when its 10+ the result is wrong.
What is the best way to make {1,2,3,4,5,6,7,8,9} … 1-99

Below:
Left 2 columns data, right 2 are results using above


[TABLE="class: grid, width: 500, align: left"]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]Extras[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Accessories Colour[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 352"]
[TR="class: grid"]
[TD="width: 352, colspan: 2"]Windows[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1x30003150, 3x25002770, 2xMotor, 1xPAD, 3xInso[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD]Heritage Red[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1x25005100, 1xMotor, 1xPAD[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD="width: 180"]Mist Green[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]2x25002770, 1xPAD, 1 xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD="width: 180"]Mountain Blue[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]3[/TD]
[TD][TABLE="width: 131"]
[TR]
[TD="class: xl67, width: 131"]Result should be 13[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1x30003150, 2x25002770, 9xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Smooth Cream[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD="width: 180"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]2x25002770, 4xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD="width: 180"]Smooth Cream[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]2x25002770, 12xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Mountain Blue[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
[TR="class: grid"]
[TD="width: 180"]TOTAL[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]2x25002770, 1xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Slate Grey[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]2xInso, 6xSideOB, 4xGableOB[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Mist Green[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1x25002770, 1xPAD, 1xWindow[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Smooth Cream[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1x25002770, 1xPAD, 1xWindow, 1xInso[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Heritage Red[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
[TR="class: grid"]
[TD="width: 309"]1xWindow, 1xSideOB, 2xGableOB[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
[TR="class: grid"]
[TD="width: 125"]Mountain Blue[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[AlphaFrog]

ROW(INDIRECT("1:99") will make an array {1,2,3...99}

=SUM(COUNTIFS(D5:D15,"Mountain Blue",A5:A15,"*"&ROW(INDIRECT("1:99")&"xWindow*")*ROW(INDIRECT("1:99"))

But then 12xWindow will be counted twice as 2xWindow and 12xWindow
I don't know how to get around that. I'm sure someone clever will figure it out.

 

[AlphaFrog]

If each entry in the EXTRAS column began with a space character, then this would work...

=SUM(COUNTIFS(D5:D15,"Mountain Blue",A5:A15,"* " & ROW(INDIRECT("1:99")&"xWindow*")*ROW(INDIRECT("1:99"))

 

[sx200apul]

If each entry in the EXTRAS column began with a space character, then this would work...

=SUM(COUNTIFS(D5:D15,"Mountain Blue",A5:A15,"* " & ROW(INDIRECT("1:99")&"xWindow*")*ROW(INDIRECT("1:99"))

I tried to apply this but Excel rejected it..
see below.. to my sheet as above..
=SUM(COUNTIFS(B5:B15,"Mountain Blue",A5:A15,"* " & ROW(INDIRECT("1:99")&"xWindow*")*ROW(INDIRECT("1:99"))

Just gave an Excel error popup, like Excel couldn't read it.. no result at all..

Any ideas?

 

[AlphaFrog]

=SUM(COUNTIFS(B5:B15,"Mountain Blue",A5:A15,"* " & ROW(INDIRECT("1:99"))&"xWindow*")*ROW(INDIRECT("1:99")))