Hello Toppers,
I did that, and when I did I gor "#N/A" for answer, between row 15 & 188 I
have empty cells in the first sheet. Cells that do not have input yet.

"Toppers" wrote:

Change the $D$14 to $D$188

from:

=IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188))

to:


=IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$ E$188="A325"),Sheet1!$C$9:$C$188))


"Serge" wrote:

There is a . after the word "answer"

"Serge" wrote:

Hello again Max,
Still have #N/A for answer as long as there are input in those rows in the
first sheet. I changed the row back to it's original number and it works fine.
I do need to include all the rows even though there is no input in them.
Thanks for your effort.
Serge in BC Canada


"Max" wrote:

"Serge" wrote:
.. My question refered to empty cells on the first sheet,
because in the given formula I changed the row number 14 to 188
which do not have any input yet.

All the ranges in the sumproduct formula have to be identical in structure.
Ensure that this is the case (that's probably why you got the #N/A earlier)

Anyway, here's the revised formulas extended to row 188 for reference

In C20, copied to C22:
=IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188))

In C32, copied to C34:
=IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---



"Serge" wrote:

On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an
example.
Column: C is for quantities, D is for sizes, E is for grades, J is for length
: C D E J
------------------------------------------
9: 4 .625 SAE 1.50
10: 6 .625 SAE 1.50
11: 4 .500 A325 1.75
12: 6 .500 A325 1.75
13: 8 .500 SAE 1.50
14: 6 .75 A325 2.25

On sheet two (summary sheet) I have the following as example:
Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and
rows 32, 33, 34 for SAE.
I would like to see a total quantity shown in column C if I type in size in
column A and length in column B for each sizes of same grade & lenght.

I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this
problem please.
Thank you in advance.
Serge