MS Excel is driving me mad!?

Question

does anyone know how to get excel to count the number of occurances of a value dependant on a condition? eg two columns of data one is gender the other is nationality, I want to count the number of female british and male italians.

filters are good but can't use for producing charts
sumif and countif work in individual columns but not with condition nor do array formulas

counting males in one gender column is ok, can do that fine, and the same in the nationality column, would like to give condition to this count, eg male and italian or female and thai or whatever!

sushine - would love to contact you but for some reason can't through this you might need to check your contact options in the profile section!!

Answer 1

I have listed a few tutorial sites in the list of sources but probably the best is this one:
http://www.bized.ac.uk/timeweb/excel.htm

Answer 2

Three ways to do this:

1. Pivot Tables. I wrote another answer explaining the basics of using them. See the link.

2. SumProduct formula. SumProduct is SumIf on steroids. SumProduct can use multiple criteria, unlike SumIf.

Suppose Gender is in column A and Nationality is in column B. In Cell C1, type in:
=SUMPRODUCT ((A2:A5000="Male")*( B2:B5000="Italian")*1))
and drag down to row 5000

Column C will have a 1 or 0 depending on the results of both comparisons.

NOTES:
Range MUST start in row 2
Both ranges MUST be equal (i.e. 5000)
Ranges can be as large as you need them to be
Do not use too many of these formulas as your calculation time will slow down to a crawl.

3. If calculation is too slow, do a similar comparison with formulas that are not loaded into memory (like SumProduct) if your comparisons are simple.

To find British females,
=if(and(A1="Female", B1="British"),1,0)

To find British and Italian females, things get a bit more complex
=if(or(and(A1="Female",B1="British"), and(A1="Female", B1="Italian")),1,0)

The second formula basically has two AND functions nested insise an OR function.

Hmmm...now that I think of it, you could rewrite it as:
=IF(AND(A1="Female", OR(B1="British",B1="Italian")),1,0)

Hope this helps. Send me an email if you need more help.

Answer 3

I agree with previous person sushant - pivot tables the way to go. Once you start using them - u'll never look back. Pivot tables offer you a summary table - where you can drill down on the specific totals to get a list of that total only. Good news is they can work on lists longer than your standard excel page - but then you need MS Access for that.

Answer 4

There's a useful function COUNTIF which counts based on a criteria you set. There's also a SUMIF function which does the same but sums rather than counts

Answer 5

in case you're speaking about video clips, CDs, etc. that's prohibited. that's stealing the paintings of different individuals. some day, once you're making a video, someone would pirate it and earn better money from it than you'll. in case you're speaking about piracy on the intense seas..... actually everyone appears commonly used with in reality the romanticized variations of piracy that they see in video clips, and picture that all pirates seem as if action picture stars. they are blind to the cruelty of authentic pirates, who were a murderous bunch of reduce-throats and entirely and not using a shred of human decency. They were the dregs of society who were hanged by ability of the neck until eventually lifeless at the same time as they were stuck. There are nevertheless authentic-existence pirates off the coast of Africa and in the far East. I actually don't have any desire to satisfy any of them. Piracy is the worst style of armed robbery. human beings were tortured, maimed, and murdered. females were raped and murdered. definite, piracy is inaccurate and that's prohibited. Kidnapping, homicide, rape, torture, and robbery are crimes. what style of info do you elect and desire?

Answer 6

Click on the top toolbar data, then filter, then auto filter. You will then have filters on each column so you can pick what info you require

Answer 7

Use the OR function.
OR(c4="female",d4="british")
Then do a countif on "TRUE"

Answer 8

this sounds more like a database appication not a spreadsheet app.

Answer 9

Hey, Pivot tables is the best way to sovle your problem. If you want to know how to use them, please feel free to contact me on my ID. CHeers

Answer 10

you have to use a formula eg. A1+B1=C1