=sumif

[Francis Hookam]

A1:A31 has text item (dimensions of boxes, the same dimensions appearing in
different rows) and B1:B31 has the quantities in each row

I need to sum the combined quantities of the same box sizes in B33 to B36
against the different box sizes in A33 to B36

How can I use the box size in A33 to sum the numbers in B1:B31 when the same
box size is found in A1:A31?

Wow! I hope that's clear

Francis Hookham

A B
1 Box size No. of boxes
2 290/375/150 629
3 290/375/150 115
4 290/375/150 433
5 290/375/150 16
6 290/375/150 33
7 290/375/150 1
8 365/375/190 4
9 365/375/190 146
10 365/375/190 3
11 365/375/190 300
12 365/375/190 66
13 365/375/190 2
14 365/375/190 43
15 365/375/190 66
16 365/450/230 1
17 365/450/230 13
18 365/450/230 10
19 365/450/230 3
20 440/450/230 6
21 440/450/230 14
22 440/450/230 34
23 440/450/230 13
24 440/450/230 29
25 440/450/230 6
26 440/450/230 1
27 440/450/230 2
28 515/450/230 1
29 515/450/230 1
30 515/450/230 6
31 515/450/230 1
32
33 290/375/150 =sumif(A1:A31,B1:B31,A33)
34 365/375/190 ?
35 440/450/230 ?
36 515/450/230 ?

 

[J Laroche]

Type or copy in B33:
=SUM(IF(A33=A$2:A$31;B$2:B$31))
and validate the formula with shift-control-enter. You should then see, in
the formula bar:
{=SUM(IF(A33=A$2:A$31;B$2:B$31))}
Do not enter the braces yourself. They mean this is an array formula. Then
copy the formula down by dragging the little square at the bottom right of
the cell.

By the way you forgot one size in your summary: 365/450/230

JL
Mac OS X 10.3.7, Office v.X 10.1.6




Francis Hookam wrote on 2005/01/15 14:58:

 

[Peo Sjoblom]

Another way that can be entered normally and is slightly faster

=SUMPRODUCT(--(A$2:A$31=A33),B$2:B$31)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[J Laroche]

Hi Peo,

These two dashes after the first parenthesis are unknown to me, and I
couldn't find any help topic on that subject. Can you direct me to any
information about it, or explain briefly how it works?

JL



Peo Sjoblom wrote on 2005/01/15 18:22:

 

[JE McGimpsey]

These two dashes after the first parenthesis are unknown to me, and I
couldn't find any help topic on that subject. Can you direct me to any
information about it, or explain briefly how it works?

Check out

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Francis Hookam]

Thanks JL and PS

Did not get on well with this - maybe did not try enough

Type or copy in B33:
=SUM(IF(A33=A$2:A$31;B$2:B$31))
and validate the formula with shift-control-enter. You should then see, in
the formula bar:
{=SUM(IF(A33=A$2:A$31;B$2:B$31))}

This was great:

=SUMPRODUCT(--(A$2:A$31=A33),B$2:B$31)

Francis Hookham