none
SUMPRODUCT IFS?

    Question

  • Trying to figure out a SUMPRODUCT IFS formula for the following.

    Would like to SUMPRODUCT Number Widgets x Gross Price.

    Text criteria are both in column D, sum range is column E.

    Tried this, but it returns zero:

    =SUMPRODUCT(--(D39:D61="Number Widgets"),--(D39:D61="Gross Price"),E39:E61)

    Any assistance would be greatly appreciated. Thanks.

    COLUMN D                        COLUMN E

    Number Widgets           4,031
    Net Widget Price         171.36
    Tax 10%            17.14
    Fees           11.40
    Gross Price         199.90
    Number Widgets           4,016
    Net Widget Price         135.55
    Tax 10%            13.55
    Fees           10.80
    Gross Price         159.90
    Number Widgets           1,427
    Net Widget Price           81.00
    Tax 10%              8.10
    Fees           10.80
    Gross Price           99.90

    Monday, May 12, 2014 10:22 PM

Answers

  • Assuming that Gross Price will always be 4 cells below Number Widgets:

    =SUMPRODUCT(--(D39:D61="Number Widgets"),E39:E61,E43:E65)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mikf Tuesday, May 13, 2014 4:20 PM
    Tuesday, May 13, 2014 5:31 AM

All replies

  • Assuming that Gross Price will always be 4 cells below Number Widgets:

    =SUMPRODUCT(--(D39:D61="Number Widgets"),E39:E61,E43:E65)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mikf Tuesday, May 13, 2014 4:20 PM
    Tuesday, May 13, 2014 5:31 AM
  • As usual Hans, that works perfectly.

    I don't *completely* understand why, but thanks ..!

    Tuesday, May 13, 2014 4:21 PM
  • You want to multiply E39 with E43, E40 with E44, etc., and sum the products

    That would be =SUMPRODUCT(E39:E61,E43:E65)

    To sum only if column D contains "Number Widgets", we add --(D39:D61="Number Widgets")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 13, 2014 4:38 PM