Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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'< |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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'< |
#4
|
|||
|
|||
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'< |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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'< |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting in pivot table | Excel Worksheet Functions | |||
Counting Rainfall Data | Excel Discussion (Misc queries) | |||
Counting Function Dilemma | Excel Worksheet Functions | |||
counting entries between two dates? | Excel Worksheet Functions | |||
counting | Excel Worksheet Functions |