Sumproduct or??

[Terry]

If I have two columns of numbers:

1 50
2 40
1 20
4 10
3 30
1 50

How do I write a formula that will sum every number in column B that
corresponds to a 1 in column A.

The answer should be 120.

Thanks for any help.

 

[Brian]

I believe you will want to use a sumif() function here

if your data starts in A1 then

=sumif(A1:A6,1,B1:B6)

 

[jlepack]

A pivot table would give you the solution you're looking for quite
suitably.

 

[Dave Peterson]

=sumif(a1:a10,1,b1:b10)
or
=sumif(a:a1,1,b:b)

=sumproduct() will be useful when you add a second column (Column A has to be 1
and column C has to be "Dept 499")

 

[Terry]

=sumif(a1:a10,1,b1:b10)
or
=sumif(a:a1,1,b:b)

=sumproduct() will be useful when you add a second column (Column A has to be 1
and column C has to be "Dept 499")

That worked! But I actually have 4 columns. I thought I could just
drag it over.

How would you write the formula if your data looked like this:

1 50 3 40
2 10 1 20
1 30 2 30
3 20 7 50
1 40 1 10

I want to sum everything with a 1 in column A and C. The answer
should be 150.

Thanks again you guys.

 

[Roger Govier]

Try

=SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D110)

 

[Dave Peterson]

I'd just use two =sumif()'s.

=sumif(a:a,1,b:b) + sumif(c:c,1,d:d)

There was a typo in this line:

=sumif(a:a1,1,b:b)

A:A1 should have be A:A.

 

[Terry]

Try

=SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D110)

Thanks again. One last question.

With your help (and Dave Peterson) I now have this formula:

=SUMIF($Y$5:$Y$34,1,$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,1,$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,1,$AD$34:$AD$54)


Is threre a way to drag that down so I can count for the numbers 1
through 7? I would put the above formula in B1 to count all of the
instances of 1, but then I would have to change all of the ones to
twos for the second row, etc. Is there a way to write the formula so
you can just drag it down?

Thanks much.

1
2
3
4
5
6
7

 

[RagDyeR]

Do you have a typo in the 3rd part of your formula, the Column AD
references?

As to automatically incrementing your criteria, try this, with AD amended:

=SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Try

=SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D110)

Thanks again. One last question.

With your help (and Dave Peterson) I now have this formula:

=SUMIF($Y$5:$Y$34,1,$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,1,$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,1,$AD$34:$AD$54)


Is threre a way to drag that down so I can count for the numbers 1
through 7? I would put the above formula in B1 to count all of the
instances of 1, but then I would have to change all of the ones to
twos for the second row, etc. Is there a way to write the formula so
you can just drag it down?

Thanks much.

1
2
3
4
5
6
7

 

[Terry]

Do you have a typo in the 3rd part of your formula, the Column AD
references?

As to automatically incrementing your criteria, try this, with AD amended:

=SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34)

Yes, I see the typo. Thanks I'll try your formula.

 

[Terry]

Do you have a typo in the 3rd part of your formula, the Column AD
references?

As to automatically incrementing your criteria, try this, with AD amended:

=SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34)

--

HTH,

RD

That worked great!

Thanks, so much.

(How do you guys know all this stuff?)

 

[Ragdyer]

Thanks for the feed-back.