COMPARATIVE COUNTING

[Iain Halder]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Iain Halder]

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

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

Rescued Cats & Kittens Needing Homes

 

[duane]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Pierre Leclerc]

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

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




Rescued Cats & Kittens Needing Homes

Pierre Leclerc
http://www.excel-vba.com
(e-mail address removed)

 

[Bob Phillips]

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

 

[Iain Halder]

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

 

[Bob Phillips]

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