Results 1 to 5 of 5

SUMPRODUCT, many results... none the expected

Threaded View

  1. #1
    Registered User
    Join Date
    10-07-2018
    Location
    Bordeaux, France
    MS-Off Ver
    2010
    Posts
    8

    SUMPRODUCT, many results... none the expected

    Hello all.

    I'm using SUMPRODUCT to give me a tally of how many times a given value in row X corresponds column-wise with another value in row Y. This involves several numeric values (between 0 and 4) and one non-numeric value ("Ø"). Here are some examples of the formulae which do work:

    =SUMPRODUCT((D109:X109="Ø")*(D107:X107=2))

    =SUMPRODUCT(($D120:$X120=0)*($D118:$X118=1))

    In fact, all of the formulae work perfectly except for the one needed to tally corresponding 0s in both rows. I have tried several declinations of the formula, but none give me the expected result (unless by coincidence). I believe the problem stems from SUMPRODUCT recognizing the non-numerical cells as 0s, but I can find no way of solving it, and my different solutions give different results but none of them the expected one.

    In the below example, you can see two formulae I have tried. The expected result is 1, corresponding to the co-occurrence of 0 in V96 and V98.
    (Please excuse my French... Excel! SOMMEPROD = SUMPRODUCT, and semi-colons ";" function as commas ",")

    SOMMEPROD 0.png

    SOMMEPROD 2.png

    Any help is very much appreciated.
    Last edited by metaphysiology; 11-16-2018 at 12:06 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula not giving expected results
    By billfinnjr in forum Excel General
    Replies: 7
    Last Post: 08-15-2018, 09:58 AM
  2. Replies: 12
    Last Post: 05-19-2016, 11:08 PM
  3. Formula not returning expected results
    By Mlabrec in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-07-2016, 08:08 PM
  4. [SOLVED] Not getting the expected results from conditional formatting
    By thoughtreactor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2015, 12:58 PM
  5. saving a xls as a csv - not getting expected results
    By Caconz in forum Excel General
    Replies: 4
    Last Post: 08-23-2010, 06:40 PM
  6. Macro is not generating expected results.
    By Foxcan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2009, 04:16 PM
  7. Sumproduct formula not giving expected results
    By Shocked in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-30-2008, 03:12 PM

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