Excel formula Question

[tweetyrm]

Here goes
I have three columns of data I want to have the computer to tell me how many fans I have from a certain location.
example: B 1 Fan
B 1 Boot
c 1 Fan
B 1 Car
B 1 Fan
I can use a countif statement to tell that I have three fans but, I want something that will tell me how many fans do I have that are from the B location in column a. and then how many boot from the b location etc...
Thanks

 

[Mark W.]

Have you considered using a PivotTable?

 

[Anne Troy]

Check here for info:

http://www.cpearson.com/excel/array.htm

 

[Audiojoe]

Best thing to use is the sumproduct formula, works like this:

=SUMPRODUCT((A:A="B")*(B:B="1")*(C:C="Fan"))

This will look down the 3 columns. If there's a 'B' in column A with a '1' next to it in column B and a 'Fan' next to that in column C it'll return a 1 and do the same all the way down the page.

Obviously you can tinker with this to make it count what you want

Audiojoe
I give love a bad name

 

[Aladin Akyurek]

On 2002-04-03 07:46, tweetyrm wrote:
Here goes
I have three columns of data I want to have the computer to tell me how many fans I have from a certain location.
example: B 1 Fan
B 1 Boot
c 1 Fan
B 1 Car
B 1 Fan
I can use a countif statement to tell that I have three fans but, I want something that will tell me how many fans do I have that are from the B location in column a. and then how many boot from the b location etc...
Thanks

=SUMPRODUCT((A2:A6="B")*(C2:C6="Fan"))

will for example give you the desired count.

Try also using PivotTables.