How to sum selected values from a table?

[Thomas Jedenfelt]

Hello all,

I hope someone can help me with this problem:

From a table, I want to summerize the value (column 2)
of all posts that has a specific text (column 1).

This text should be the same as in an unique cell.

Exemple:

Book 1
Record 2
Magazine 3
Book 4
Record 5
Book 6

Unique cell text: [book]

Number of books: [11]

What is the formula in the cell which shows the value 11?

If you know a solution, just copy the formula in your
reply (instead of trying to explain in writing, which
might be rather tiring). Hopefully, I can figure out
how to make it work.

(I have been using Excel for several years, for my
personal need.)

Thanks and Regards
Thomas Jedenfelt
Tyresö (Stockholm, Sweden)

 

[Leo Heuser]

Hej Thomas

One way:

=SUMPRODUCT((A1:A100="Book")*B1:B100)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[Thomas Jedenfelt]

Hej Leo,

Tack så mycket för ditt svar.

Thank you very much for your formula.
It was exactly what needed!
You made my day!!


Another problem came up:

I would like to have the sum of 'Record'
added to the sum of 'Book'.

The total should then be 18 (11+2+5).

I have tried with the function [OR], but without luck.
Below formula works, but I'm sure it can be done
in a more proper manner.

=SUMPRODUCT((A1:A100="Book")*B1:B100)+SUMPRODUCT((A1:A100="Record")*B1:B100)


Thanks again.

Hilsen
Thomas Jedenfelt

P.S.
It's very nice to get help from a MVP
(Microsoft Most Valuable Professional).

 

[Leo Heuser]

Hej igen, Thomas, og velbekomme.

Yes, there is a shorter way:

=SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100)

or, if the names of the items to search for are in e.g. F1:G1
(Book in F1 and Record in G1)

=SUMPRODUCT((A1:A100=F1:G1)*B1:B100)

You can add items in H1, i1, J1 etc. and adjust the
formula accordingly, e.g.

=SUMPRODUCT((A1:A100=F1:J1)*B1:B100)


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

Hej Leo,

Tack så mycket för ditt svar.

Thank you very much for your formula.
It was exactly what needed!
You made my day!!


Another problem came up:

I would like to have the sum of 'Record'
added to the sum of 'Book'.

The total should then be 18 (11+2+5).

I have tried with the function [OR], but without luck.
Below formula works, but I'm sure it can be done
in a more proper manner.

=SUMPRODUCT((A1:A100="Book")*B1:B100)+SUMPRODUCT((A1:A100="Record")*B1:B100)


Thanks again.

Hilsen
Thomas Jedenfelt

P.S.
It's very nice to get help from a MVP
(Microsoft Most Valuable Professional).


"Leo Heuser" <[email protected]> wrote in message

Hej Thomas

One way:

=SUMPRODUCT((A1:A100="Book")*B1:B100)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[Thomas Jedenfelt]

Hej igen Leo,

Thanks again for taking the time to help.


Your shorter formula did not work.
(I copied and pasted it.)
=SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100)

The error pop-up said 'wrong formula'.
I presume that curley brackets {} is
for delimiting a matrix area.
(I use PC Win98, Excel 97)

It's okay, though. I can use the long formula (Message #3).


Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100)
for selection criteria (F1:G1) did work, but only
within rows. Columns did _not_ work.
For example (selection criteria cells F1:F2)
=SUMPRODUCT((A1:A100=F1:F2)*B1:B100)

If you (or anyone else) have a solution,
that would be good. If not, that's okay.
I probably can make a work-around, when the needed.

Tack igen.

Hilsen
Thomas Jedenfelt

 

[Leo Heuser]

Hej igen igen

Hej igen Leo,

Thanks again for taking the time to help.


Your shorter formula did not work.
(I copied and pasted it.)
=SUMPRODUCT((A1:A100={"Book","Record"})*B1:B100)

The error pop-up said 'wrong formula'.
I presume that curley brackets {} is
for delimiting a matrix area.

Yes, it's a matrix of constants {"Book","Record"}
Be aware that the delimiter (here comma) must be
the localized equivalent, which in Sweden is semicolon,
so changing {"Book","Record"} to {"Book";"Record"}
should make the formula work.

(I use PC Win98, Excel 97)

It's okay, though. I can use the long formula (Message #3).


Your formula =SUMPRODUCT((A1:A100=F1:G1)*B1:B100)
for selection criteria (F1:G1) did work, but only
within rows. Columns did _not_ work.
For example (selection criteria cells F1:F2)
=SUMPRODUCT((A1:A100=F1:F2)*B1:B100)

In order to make it work for columns, you have to use the
TRANSPOSE-function like this:

=SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100)
or
=SUM((A1:A100=TRANSPOSE(F1:F2))*B1:B100)

Both formulae must now be entered with <Shift><Ctrl><Enter>, also
if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { }. Don't enter
these brackets yourself.
Using TRANSPOSE sets a maximum of 5461 items (F1:F5461),
but that's probably not a problem in this context

 

[Thomas Jedenfelt]

Hej Leo,

Yes, I have a Swedish version of Excel 97, and changing
the operator from comma (,) to semicolon ( in the matrix
of constants {"Book","Record"} did work.

(I should have figured that out myself, as I have learned
much about Excel formulae by trial and error <smile>.)

With your TRANSPOSE-function, I now have the option to have
the selection criteria cells listed row by row (F1:F2),
instead of column by column (F1:G1).
=SUMPRODUCT((A1:A100=TRANSPOSE(F1:F2))*B1:B100)

(It took a while to get the formulae to work,
as I forgot how to enter this kind of
formulae: <Shift><Ctrl><Enter>, which you mentioned.
<smile>)


I think I should mention how I am using the formulae
you have helped me with. It's to get an overview of
my economy. I have one sheet for transactions and
another for summaries (overview).

The formulae below are for summarizing transaction types.
Without the formulae, I had to do some summarizing
_manually_, with the risk of making errors.
=PRODUKTSUMMA((Okt!D343=D10)*Okt!E3:E43)
=PRODUKTSUMMA((Okt!C3:C43={3020;3030;3040;3050})*Okt!F3:F43)


To give something in return for all your help,
here are four useful links:

Google Labs
http://labs.google.com/
I recommend 'Search by Location' and 'News Alerts'.

timeanddate.com
http://www.timeanddate.com/worldclock/city.html?n=69
World Clock and Calendar, by a Norwegian guy.

All Music Guide (AMG)
http://www.allmusic.com/
Artist biographies and discographies.

Netcraft
http://www.netcraft.com/
Platform detection.


Thank you very much!

Hilsen
Thomas Jedenfelt,
a more content guy than prior to 23rd Oct.
<smile>

 

[Leo Heuser]

Hej Thomas

You're welcome, and thanks for the feedback.
I'll give the links a try

Med venlig hilsen
Leo Heuser