COMPARATIVE COUNTING
 

=SUMPRODUCT(--(A1:A100="value1"),--(B1:B100="value2"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Hello,

In an EXCEL '97 spreadsheet I need to be able
to do a comparative count.

How many times do item one AND item two
occur from a series of many other options?

I tried putting two countif's together but all it did was add the
total of the two fields together and not how many times item one and
two coincided together at the same time.

This one has me stuck and thanks in advance anybody who can be of
help!!!

Iain Halder
Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<

this formula works fine in my version of excel (98 I believe)

=sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital"))

presumably the ranges a1:a100 and b1:b100 are correct?


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=319070

That is exactly what I gave you, you don't enclose numbers in quotes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Bob,

Thank you for replying!

I tried this as you suggested but the cells came up blank.

The comparison is not between numbers but between texts

i.e. =sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital"))

The idea is to do a count of people who attend A&E and who are then
admitted to the hospital. There are many other options in these two
columns but I need to be able to quickly count these particular two.

Am I doing this wrong in some way?

Iain Halder


On Sat, 20 Nov 2004 20:24:53 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A1:A100="value1"),--(B1:B100="value2"))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<

Iain,

Another thought, is the text in the column exactly A&E and Admit to
Hospital. Try this variation

=SUMPRODUCT(--(ISNUMBER(FIND("A&E",A1:A100))),--(ISNUMBER(FIND("Admit to
Hospital",B1:B100))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Bob,

Thank you for replying!

I tried this as you suggested but the cells came up blank.

The comparison is not between numbers but between texts

i.e. =sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital"))

The idea is to do a count of people who attend A&E and who are then
admitted to the hospital. There are many other options in these two
columns but I need to be able to quickly count these particular two.

Am I doing this wrong in some way?

Iain Halder


On Sat, 20 Nov 2004 20:24:53 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A1:A100="value1"),--(B1:B100="value2"))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<

Hi

the formula is:

=sumproduct((B1:B100="Admit to
Hospital")*(A1:A100="A&E")*(A1:A100="A&E"))

See other example of SUMPRODUCT at hte address below

http://www.excel-vba.com


On Sun, 21 Nov 2004 05:09:35 +0000 (UTC), Iain Halder
wrote:

Bob,

Thank you for replying!

I tried this as you suggested but the cells came up blank.

The comparison is not between numbers but between texts

i.e. =sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital"))

The idea is to do a count of people who attend A&E and who are then
admitted to the hospital. There are many other options in these two
columns but I need to be able to quickly count these particular two.

Am I doing this wrong in some way?

Iain Halder


On Sat, 20 Nov 2004 20:24:53 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A1:A100="value1"),--(B1:B100="value2"))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<

Pierre Leclerc
http://www.excel-vba.com

The formula that I gave is just as valid as with a * operator (see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation), but
what value is there in testing A1:A100 for A&E twice?

Bob

"Pierre Leclerc" wrote in message
...
Hi

the formula is:

=sumproduct((B1:B100="Admit to
Hospital")*(A1:A100="A&E")*(A1:A100="A&E"))

See other example of SUMPRODUCT at hte address below

http://www.excel-vba.com


On Sun, 21 Nov 2004 05:09:35 +0000 (UTC), Iain Halder
wrote:

Bob,

Thank you for replying!

I tried this as you suggested but the cells came up blank.

The comparison is not between numbers but between texts

i.e. =sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital"))

The idea is to do a count of people who attend A&E and who are then
admitted to the hospital. There are many other options in these two
columns but I need to be able to quickly count these particular two.

Am I doing this wrong in some way?

Iain Halder


On Sat, 20 Nov 2004 20:24:53 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A1:A100="value1"),--(B1:B100="value2"))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<

Pierre Leclerc
http://www.excel-vba.com

Iain,

Glad you are sorted, but what was wrong with the formula
=sumproduct(--(A1:A100="A&E"),--(B1:B100="Admit to Hospital")) that you said
returned nothing?

As a cat lover, you'll get all the support I can offer in the light of yourv
work.

Bob

"Iain Halder" wrote in message
...
Bob & Pierre!

These SUMPRODUCT variations all worked and worked beautifully to!!

In fact, in applying them and then seeing the results, I discovered
some errors already in my current approach which means I need to
refine the original database to make it more accurate.

I have to thank you guys because I'm doing a database project which
relates to hospital bed management and which will have an effect on
getting resources from the government.

These simple (to you guys) solutions which (to me) seem insurmountable
problems (so I end up doing manual counting approaches) are really
helpfull and I appreciate your (quick) advice.

Iain Halder


Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<