Matrix formula for vertical and horizontal sumif's ?

[sabinezp]

Hi there,

I am looking for a formula that could return the result for a look up request with data like this:

123 456 123
70 1 1 1
30 1 1 1
70 1 1 1

I would like to have the sum (or count) of all cells (containing 1 here) where 1) the kolums start with 123 and the rows with 70, so there would be 2 arguments and the result here would be 4. The column headers / row labels would NOT be unique and the real tabel would be much bigger (more kolumns, more rows).

Who knows a matrix formula that could do the trick? Thanks in advance ,

Sabine

[Pepe Le Mokko]

=SUMPRODUCT((B3:B5=70)*C3:E5*(C2:E2=123)) e example when number of columns=nr of rows

The number of columns and rows should be the same. If not, just add references so they do, even if you add references to empty columns/rows ( see attachment)

[delozory]

Pepe Le Mokko's solution is excellent; however, I do not believe there is a need for a constraint that the number of columns referenced must equal the number of rows referenced.

-Toby

[sabinezp]

Pepe, thank you very much for your excellent solution, it works like a charm (I have replaced the values in the formula with the cel references). And even better: not a matrix formula needed! I knew the SUMPRODUCT formula but did't realize that it would work here!

Thanks again,

Sabine

[Pepe Le Mokko]

Graag gedaan

[Pepe Le Mokko]

Never had so much rep for an answer. Thanks to all of you