SUMPRODUCT or SUMIF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Serge said:
On sheet one I have:
Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14
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)
Columns: A (size), B (length), C (quantities)
& rows 20, 21, 22 for A325 and
rows 32, 33, 34 for SAE.
.. 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 & length.
Click to expand...

In Sheet2,

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

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


---
 
On Sheet2, enter this formula in C20:

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

And copy down to C22.

In C32 enter:

=SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$J$14=B32)*(Sheet1!$E$9:$E$
14="A325")*Sheet1!$C$9:$C$14)

And copy down to C34.
 
Of course the second formula should refer to "SAE".

=SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$J$14=B32)*(Sheet1!$E$9:$E$
14="SAE")*Sheet1!$C$9:$C$14)
 
Ragdyer said:
... In C32 enter:
=SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$J$14=B32)*(Sheet1!$E$9:$E$
14="A325")*Sheet1!$C$9:$C$14)
Click to expand...

Think Ragdyer meant "SAE"
instead of "A325" for the formula in C32 <g>,
viz:

In C32:
=SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$J$14=B32)*(Sheet1!$E$9:$E$
14="SAE")*Sheet1!$C$9:$C$14)

---
 
Hello Max,
It work well until I copied the formula to empty cell to give me a "#NA" for
answer. can the formula be modified?
 
Serge said:
.. It work well until I copied the formula
to empty cell to give me a "#NA" for
answer. can the formula be modified?
Click to expand...

I'm not sure how you got that #N/A error, but you could
try these 2 slight variations* to what I suggested earlier
(note that my suggestion is a little different from Ragdyer's)
*with a simple error trap to check on col A & B having inputs

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

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

---
 
Hello Max,
Thanks for your reply.
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.
 
Serge said:
.. 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.
Click to expand...

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))


---
 
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
 
There is a . after the word "answer"

Serge said:
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
Click to expand...
 
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))
 
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: Thanks for the correction ! My error.

Serge: Glad to see you resolved it in your new post

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2002 VLOOKUP formula or other formula 4
What type of formula? 6
SUMPRODUCT formula expanded from original 3
SUMPRODUCT 1
sumproduct or sumif? 3
SUMPRODUCT formula help 7
sumif only seeing one row 3
Excel Countifs/Sumproduct with mutil Or statement 3

Back
Top