Results 1 to 2 of 2

Countifs on an Array Range using multiple criteria in an Array

Threaded View

Mysore Countifs on an Array Range... 09-11-2016, 09:45 PM
Mysore Re: Countifs on an Array... 09-12-2016, 04:39 PM
  1. #1
    Forum Contributor
    Join Date
    01-03-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2016,Excel 2013
    Posts
    186

    Countifs on an Array Range using multiple criteria in an Array

    Hi Guys

    I'm trying to use Countifs on a contiguous range using multiple criteria as an array but the formula throws out an error.
    If I individually choose the criteria range along with a single criteria , the formula works. In my situation I have 16 Columns on which I need to perform a Countifs along with 16 criterias , hence looking for an Array solution rather than adding Criterias 16 times
    Any help is much appreciated.

    Below is my formula which gives error

    {=SUMPRODUCT(COUNTIFS(Date,$O9,Product,SoldWhat,SalesRep,$P9,Store,Where,Table1[[Online Sale]:[Achieved Margin]],$H$3:$K$3))}

    Formula which works

    =SUMPRODUCT(COUNTIFS(Date,$O9,Product,SoldWhat,SalesRep,$P9,Store,Where,Table1[Online Sale],$H$3,Table1[Cash Sale],$I$3,Table1[Not Discounted],$J$3,Table1[Achieved Margin],$K$3))


    Attached is a sample file.

    Thanks

    Mysore
    Attached Files Attached Files
    Last edited by Mysore; 09-12-2016 at 04:38 PM.
    Don't forget to rate 1 who helped u, using "Star"

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  2. [SOLVED] Add multiple criteria to auto filter one by one via loop from a range / array
    By Ronnet2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2016, 07:25 AM
  3. [SOLVED] Array Match and Return value upon multiple array match criteria
    By VegetaOSX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2015, 05:50 AM
  4. [SOLVED] SUMIFS/COUNTIFS array function question (multiple criteria)
    By akamenov88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2015, 07:08 PM
  5. [SOLVED] Populate one array from another array and print new array as a range
    By Kaden265 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2014, 07:52 AM
  6. Countifs multiple criteria array
    By lamdl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2014, 09:18 PM
  7. Replies: 4
    Last Post: 05-16-2008, 02:27 AM

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