Search code examples
excelexcel-formulasumifs

Excel conditional SUMPRODUCT / SUMIFS / Array Formula for optional dimension

I have a sheet of data with multiple dimensions like this:

    A         B           C        D           E  
1   COUNTRY   FLAVOUR     SIZE     DATE        SALES ($)
2   Japan     Strawberry  100ml    10/12/14    100
3   Japan     Banana      100ml    10/03/15    100
4   China     Orange      200ml    14/04/15    30
5   France    Strawberry  200ml    11/04/15    400
6   UK                    200ml    23/04/15    250
7   ....

I want to aggregate this data over a date range, where the summary sheet has each dimension (country & flavour), and if I do not specify a dimension it sums all rows for that dimension.

    A         B           C
1   COUNTRY   FLAVOUR     SALES TOTAL
2   Japan     Strawberry  100
3   Japan                 200
4             Strawberry  500

I can do this if all the dimensions are present (i.e. row 2 above) using a SUMPRODUCT or SUMIFS:

=SUMPRODUCT((data!A$2:A$100=A1)*(data!B$2:B$100=B1)*(data!D$2:D$100>[start_date]*(data!D$2:D$100<[end_date])*(data!E$2:E$100))

However I have not been able to figure out how to include all rows for a dimension if that input cell is empty (e.g. row 3 above). I tried:

One solution is to have different branches of the formula depending on which summary dimensions are present, but that would quickly get out of control if I extend this same behaviour to further dimensions like Size.

Any help appreciated!

(I'm running Excel Mac 2011).

EDIT

Per @BrakNicku's comment one of the formulas I tried was =SUMPRODUCT(((data!A$2:A$100=A2)+ISBLANK(A2))*((data!B$2:B$100=B2)+ISBLANK(B2))*(data!E$2:E$100))

The reason this doesn't work is that sometimes my data has blank attributes (edited above). For some reason this formula double-counts rows where the attribute present matches (e.g. data!A6) but the other attribute is missing (e.g. data!B6).

EDIT 2

I can see why this double-counting is happening, because the + is summing the match because data!A$2:A$100=A2 (they match because they are both blank) and the match because ISBLANK(A2) (it is indeed blank). The question would remain how to achieve this without double counting. If needed a workaround could be to fill all blank cells on my data with some placeholder value.

Solution

  • The reason for double-counting values is here:

    ((data!A$2:A$100=A2)+ISBLANK(A2))

    If a cell in A column is blank, both parts of this sum are equal 1. To get rid of this problem you can change it to:

    (((data!A$2:A$100=A2)+ISBLANK(A2))>0)