Formula Arrays (2003)

Topic

New Reply

Edited by HansV to present data in table format

I can’t figure out an array formula for counting based on multiple criteria. I am playing with a mix of COUNTA and COUNTIF formulas, but can’t seem to get it right. I want to count records that meet an Organization criteria, a Status criteria and a completion criteria (marked by “X)–and others, but I’ll figure those out after understanding the logic. Below is an example of a subset of the data. Essentially I am trying to pull a number of different counts of the data like “How many active Group A people have completed Class 3?”.

For instance:

Name	Organization	Status	Class 1	Class 2	Class 3
Joe	Group A	Active	X	X

Reply | Quote

Replies

Welcome to Woody’s Lounge!

For multiple conditions, you need a different kind of formula. SUMPRODUCT is often used for this. If your sample data are in A1:F5, the following formula will count the number of people in Group A who are active and who have completed Class 3:

=SUMPRODUCT(($B$2:$B$5=”Group A”)*($C$2:$C$5=”Active”)*($F$2:$F$5=”X”))

See attached sample workbook.

Reply | Quote

Thanks Hans!

For the SUMPRODUCT function, does order matter at all? I’m trying to think of a situation where a count is made if an EITHER/OR condition is met.

For the F2:F5 range, what if I want it to count any value and not just “X” or count “X” and one other value like “Y”?

Reply | Quote

The order of the arguments in SUMPRODUCT doesn’t matter.

If you want to count any non-blank value in F2:F5, you can use

=SUMPRODUCT(($B$2:$B$5=”Group A”)*($C$2:$C$5=”Active”)*($F$2:$F$5″”))

If you have an OR condition with mutually excluding criteria, you can use +:

=SUMPRODUCT(($B$2:$B$5=”Group A”)*($C$2:$C$5=”Active”)*(($F$2:$F$5=”X”)+($F$2:$F$5=”Y”)))

If you have overlapping conditions such as B2:B5 = “Group A” or F2:F5 = “X”, you need yet another kind of formula – post back if you need that.

Reply | Quote

Hans,
I think that do have overlapping conditions it seems. I have attached a more useful example. It is a spreadsheet that tracking employees and their completion of training course. Sheet 2 has a table that I am trying to populate with equations. PivotTables are doing nothing but confuse me.

So I am wanting to count Active employees who have completed, been recognized or completed an alternate course within specified time frames based on when they came onboard. Using SUMPRODUCT has given me numbers that I know to be incorrect. Please take a look and populate a couple and see if I can take it from there.

Reply | Quote

I inserted a column for the Org#. Here is a formula for the number of people in Org#1 onboard between .5 and 1 year:

=SUMPRODUCT((Sheet1!$B$2:$B$18=Sheet2!$B3)*(DATEDIF(Sheet1!$C$2:$C$18,TODAY(),”m”)>6)*(DATEDIF(Sheet1!$C$2:$C$18,TODAY(),”m”)<=12))

More to come…

Reply | Quote

See the attached workbook for the more complex formulas needed for the number of persons with complete etc. classes. I used an auxiliary column on Sheet1.

Reply | Quote

Okay! So the DATEDIF function is counting off of the actual Onboard Date using months–variable “m”? That simplifies things so I don’t need to have the column J on Sheet 1. Isn’t the “*” symbal for multiplying? Why wouldn’t you use the “+” symbol in the equation?

Reply | Quote

The DATEDIF function calculates the interval between two dates in the unit specified by the last argument. The result is always a whole number. We can’t use years as units here, since you want to look at multiples of 0.5 years. That is not a whole number, so we use months as unit and look at multiples of 6 months.

DATEDIF is more accurate than dividing the number of days by 365, since DATEDIF takes leap years into account.

See DATEDIF function on the Microsoft site or DATEDIF Function on Chip Pearson’s site for more info about DATEDIF.

* is indeed the multiplication operator. We use this because in Excel, TRUE corresponds to 1 and FALSE corresponds to 0. When you combine two conditions with AND, you get a 2-by-2 table as in the picture below. As you can see, the combination can be obtained by multiplying the individual values (1 = 1 * 1). That is why we use * in the SUMPRODUCT formulas.

Reply | Quote

Hans,
For some reason the function (DATEDIF(Sheet1!$C$2:$C$18,TODAY(),”m”)0) that is supposed to be summing the record values of column K that meet the criteria as opposed to it just counting the number of records that have an integer greater than 0 in column K)

Please advise.

Reply | Quote

1) The mistake is mine. If somebody has been “on board” for 6 months and 10 days, DATEDIF returns 6. We don’t want to include this person in the group <0.5 years. So I should have used <6 instead of 0 bit works as intended.

Reply | Quote

I made the changes that you recommended. However, I would like the counts in column K to be summed as opposed to just a count of the records that meet that criteria. That is, if an employee has been onboard in the certain time frame, then tally all of the classes (Completed, Recognized, Alternate) that they have completed.

Reply | Quote

That is relatively easy: change (Sheet1!$K$2:$K$18>0) to Sheet1!$K$2:$K$18, for example in cell E3 on Sheet2:

=SUMPRODUCT((Sheet1!$B$2:$B$18=Sheet2!$B3)*(DATEDIF(Sheet1!$C$2:$C$18,TODAY(),"m")<6)*Sheet1!$K$2:$K$18)

Modified version attached.

Reply | Quote