Gavin wrote:Yes, I seem to recall that things like the COUNTIFS formula were introduced in Excel 2007, which is a really useful one. I think I even used it in the LayTheField3.xls example sheet.
Kind regards,
Gavin
For anyone who only has Excel 2003, which doesn't support the COUNTIFS (or SUMIFS) functions, I found a way to reproduce these in Excel 2003 using the SUMPRODUCT function:
Source: http://codesnip.net/countifs-and-sumifs-in-excel-2003/
COUNTIFS is a new function available in Excel 2007. This has proved to be very helpful and it is always a problem if you switch back and forth between the two versions. Well, here is an equivalent for COUNTIFS that you can use until your company decides to spend those extra bucks and get everyone on to version 2007
These will be the equivalents in 2003, using SUMPRODUCT.
COUNTIFS:
Let’s assume the data is in two columns A and B. Without using COUNTIFS, if you want to count how many rows have a particular specified value in column A and another specified value in column B, where the wanted values are stored in E5 and F5:
=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5))
Where E5 has the counter and F5 has the sales-person. The same can be extended for a SUMIFS as below.
SUMIFS:
Let’s assume the data is in three columns A, B and C. You want to sum all the values in column C, where A and B are a specified value (specified in E5 and F5), without using SUMIFS:
=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5)*(C2:C10))