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)

Hej Thomas

One way:

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

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"Thomas Jedenfelt" skrev i en meddelelse
om...
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)

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" wrote in message ...
Hej Thomas

One way:

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

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

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.

"Thomas Jedenfelt" skrev i en meddelelse
om...
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" wrote in message
...
Hej Thomas

One way:

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

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

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" wrote in message ...
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

Hej igen igen


"Thomas Jedenfelt" skrev i en meddelelse
om...
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 :-)

---
Best Regards
Leo Heuser
Excel MVP


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" wrote in message
...
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