Count text strings that meet a condition

Hello, please see the formula's on sheet 6 in the attached workbook. I am trying to count the amount of times the yellow cells appear in the array named "data" on the same lines as the blue cells appear in the array named "breadtype". Obviously this is failing as the arrays are different sizes. Does anyone know of any way to do this?

Thanks for any help you can give!
Clayton Grove

This formula works if you don't have errors (#N/A) in the ranges,

=SUMPRODUCT((data=B$1)*(breadtype=$A2))

...... so either eliminate all errors or you can switch to this "array formula"

=SUM(IF(ISNUMBER((data=B$1)*(breadtype=$A2)),(data=B$1)*(breadtype=$A2)))

confirmed with CTRL+SHIFT+ENTER

Yes, arrays do have to be the same size in SUMPRODUCT.

There's a subtle difference in the syntax here that means that the arrays are multiplied first....and SUMPRODUCT then technically operates on only a single array.

You can see this with a simple example. Fill A1:C5 with numbers and try this formula

=SUMPRODUCT(A1:A5,B1:C5)

You get a #VALUE error because A1:A5 is not the same size as B1:C5.....but if you do this

=SUMPRODUCT(A1:A5*B1:C5)

You get a result because multiplying those ranges using matrix multiplication gives you a single 2x5 matrix....and SUMPRODUCT effectively just sums the resultant single array. In these cases you don't even need SUMPRODUCT, SUM would be sufficient because you are only summing a single array, i.e.

=SUM(A1:A5*B1:C5)

will do the trick.....although it needs to be "array entered" with CTRL+SHIFT+ENTER....similarly you could use

=SUM((data=B$1)*(breadtype=$A2))

array entered