Thanks:  0
Likes:  0

1. 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

2. Have you considered using a PivotTable?

3.

4. 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

5. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•