How to count cells based on 3 arguements.

[KollBrian]

Question, does anyone know how to count cells based on "if"'s from 3 columns?

I need to count the number of times certain information happens.

Ok, column A, B, C. Column A is a column of names. Column B is a column defining a type of activity. And column C contains a single number (number could be from 0 to 4000).

What I want to do is count the number of times a specific name occurs, but only if column b equals &quot;x&quot; and if column c is <60.

The formula when done is going to be used to take a report that was sorted alphabetically and sorted by pro number and tell a worksheet how many cargo loads that person had that picked up, and delivered &quot;but&quot; were more than 60 minutes late. So from a report sheet that lists 6 loads, the worksheet cell would end up saying &quot;2&quot; indicating that 2 of the loads that person had were later than 60 minutes.

Thanks guys,
Brian Koll
KollBrian@Yahoo.Com

 

[sfvb]

You could use the dcount function. For example in cell D2 the formula is:
=DCOUNT(A4:C14,C1,A1:C2)

This will count all the &quot;als&quot; who have an activity type of &quot;a&quot; and minutes of less than 60.

 

[xlbo]

Hi Brian, sounds like an array formula is what you need:

=SUM((Sheet1!B2:B200=&quot;x&quot*(Sheet1!C2:C200<60)*(Sheet1!A2:A200=A2))

Where you have a list of names on a summary sheet (starting in A2. This formula would go in B2 and be copied down. sheet1 is your base data sheet

If you want this on the same sheet as the base data, just get rid of the sheet names and obviously, if you want to hard code the names in, just substitute &quot;NameHere&quot; for A2 in the final statement


oh yes, you have to enter the formula with CTRL+SHIFT+ENTER instead of just ENTER

HTH
~Geoff~