Excel compatible versions

Questions about integrating Excel and Cymatic, programming, formulae, etc

Re: Excel compatible versions

Postby Gavin » Mon May 16, 2016 1:08 pm

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))
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Excel compatible versions

Postby Gavin » Tue Aug 08, 2017 9:11 am

Open Office is not compatible with Cymatic.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Excel compatible versions

Postby Gavin » Sun Feb 18, 2018 12:09 am

Is anyone using Excel 2016?

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Previous

Return to Excel - Help / Support

Who is online

Users browsing this forum: No registered users and 0 guests

cron