+ Reply to Thread
Results 1 to 9 of 9

Array / CSE formula problem

  1. #1
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Array / CSE formula problem

    Hey All!

    I have a problem with an array / CSE formula. Hope you can help.

    The below works just fine, but I'm having problems with COUNT(IF and AVERAGE(IF

    I've entered all array's with Ctrl Shift Enter.

    =SUM(IF('2009_3-4-w'!$T$1:$T$2000>"",IF('2009_3-4-w'!$V$1:$V$2000="Europe",IF('2009_3-4-w'!$A$1:$A$2000="Blue",'2009_3-4-w'!$O$1:$O$2000))))

    Col. T contains text, Product A, Product B, Product C, Product B -- if there's any text in here i want that row to be included in the average calculation / or count; but there may not be a number of average or calc in Col. O. (there may be a blank or there may be NA, or numbers, positive and negative)

    Col. V contains Regions, US / Europe
    Col. A contains Colours, Blue / Red
    Col. O contains numbers, 6,7, -6, -12, and NA and BLANKS (i think this may be causing a problem?)

    COUNT(IF overstates the number of items in column O, and AVERAGE(IF does not average correctly. There's quite a lot of data (900 rows or more), if I use autofilter I can select the range and grab the correct counts and averages, but not via an array formula, any ideas?

    Many thanks,

    B.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Array / CSE formula problem

    Try this, replacing ranges as appropriate:

    =SUMPRODUCT( (TTT<>"") * (VVV="Europe") * (AAA="Blue"), OOO)

    You can't have errors in OOO.
    Last edited by shg; 03-06-2010 at 03:44 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Array / CSE formula problem

    Hey thanks, I will - what will the result type be, Count?

    What's OOO, the range to count / sum?

  4. #4
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Array / CSE formula problem

    Can't get that to work. Returns zero or value error..

    Can this formula work over a range? I need to calculate those ranges from one cell.

  5. #5
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Array / CSE formula problem

    OK. I can get the formula to function but it doesn't do what I need; at least not the way I typed it.

    Any other ideas?

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Array / CSE formula problem

    Please post a sample workbook with dummy data, showing an example result and the logic if not obvious. Thanks.

  7. #7
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Array / CSE formula problem

    here you go..

    please note, sample data file has array formulas entered: 'sum if' working correctly and summing the column with IF conditions and 'average if' mis-calculating
    Attached Files Attached Files
    Last edited by benclayes; 03-06-2010 at 05:59 PM.

  8. #8
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Array / CSE formula problem

    Try
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Array / CSE formula problem

    Hey thanks,

    That's great!! I almost had what you typed there but not quite.

    Brilliant.

    Ben.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1