The sum under conditions

[Andre]

Hello.

Let's say I have a table which looks like this:

A B
word1 240
word1 22
word3 234
word2 90
word3 45
word1 567
word2 3


Now. How should the function look like if I want to count the total
amount for column B but only for the lines containig "word1"?

Meaning, for "word1" Excel should take only: 240, 22, 567 and sum it up.

I need to sum up the total amount for each "word" separately.

 

[joeu2004]

Let's say I have a table which looks like this:
A       B
word1   240
word1   22
word3   234
word2   90
word3   45
word1   567
word2   3

Now. How should the function look like if I want to count the total
amount for column B but only for the lines containig "word1"?
Meaning, for "word1" Excel should take only: 240, 22, 567 and sum it up.

A couple ways:

=SUMIF(A1:A7,"word1",B1:B7)

=SUMPRODUCT(--(A1:A7="word1"),B1:B7)

In this case, SUMIF is better; it is more efficient. But the
SUMPRODUCT can be extended to cover more complex criteria. If you
have Excel 2007 or later, you might be able to use SUMIFS (with an
"S") to cover multiple simple-selection criteria.

I need to sum up the total amount for each "word" separately.

Put each word into C1, C2, etc. Then in C1, enter the following
formula and copy down:

=SUMIF($A$1:$A$7,C1,$B$1:$B$7)

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[Andre]

joeu2004 napisał(a):

A couple ways:

=SUMIF(A1:A7,"word1",B1:B7)

=SUMPRODUCT(--(A1:A7="word1"),B1:B7)

In this case, SUMIF is better; it is more efficient. But the SUMPRODUCT
can be extended to cover more complex criteria.

I don't know why but the first one does not work for me, Sir. I get
"name" warning" for SUMIF (not for "word1"). The second one though works
just fine and helped me a lot. Thanks a million.

It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited to
the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Oh my... Good to know and thanks for the information. I will most
certainly save this address. It's a shame though that Usenet slowly but
constantly dies...