Filtering without Filter

[Karpo]

I just don't get the following. Any ideas?

"A" row I have two different values I use: "Contact" and "Not a contact".
"B" row I have the person's name (employee).
"C" row I have a quantity, just one number.

Now what I need is to output quantities of how many Contacts Employee1 or
Employee2 or Employee3 have.
Also what I need is to get the number of contacts - which can be found on
row "C".

Should I use COUNT.IF ? Row A can have 2 or more different values so should
there be somekind of conditional IF function included?

This is going to be a self-updating excel in the end that will show "Live",
who has the most contacts. I understand that using Auto-filter would easily
give me answers, but I'm afraid it's not an option if this is going to be one
big shared excel with pretty graphs.

 

[Don Guillett]

=sumproduct((a2:a22="contact")*(b2:b22="employee1")*c2:c22)

 

[Karpo]

Thank you Don Guillett! I was able to make one beautiful excel with this
matrix -function-thingy you gave me.

 

[Don Guillett]

Glad to help

 

[Max]

There's another functionality known as a pivot table in Excel which can
deliver equally beautiful results in a flash. If your 3 col headers are say:
Con, Emp & Qty, in step 3 of the pivot wizard, click Layout, then just drag n
drop Con & Emp in ROW area (one below the other), drag n drop Qty in DATA
area. That's it. Check out the pivot's results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---