2

Suppose I have the following table:

(can't embed images yet)

I'd like to find the total number of apples sold on day 1, regardless of customer sold to.

I'm somewhat familiar with INDEX/MATCH but I'm not too sure on how to proceed.

I understand that the SUMIF function can be used to sum only the desired day, but I am unsure of how to sum multiple rows with INDEX/MATCH.

The output I'm looking for is as follows:

(can't embed this either)

But most importantly, I'd like to know how to sum multiple specific rows and thus I'm requesting a formula for B16.

2
  • Does each day have fixed number of rows? In the image you've attached, it's six.
    – user313811
    Commented Mar 4, 2018 at 10:33
  • @Mahesh my bad, it doesn't. Commented Mar 4, 2018 at 10:40

2 Answers 2

2

If you have the option of unmerging the cells in column A and normalizing it, then a simple SUMIFS should work. Otherwise, try this:

=SUMPRODUCT($C$2:$C$13,
1*($B$2:$B$13=$A16),
1*(ROW($A$2:$A$13)>=SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15)),
1*(ROW($A$2:$A$13)<SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15+1)))

Result:

╔════════╦════╦════╗
║  Day   ║ 1  ║ 2  ║
╠════════╬════╬════╣
║ Apple  ║ 60 ║ 90 ║
║ Orange ║  0 ║ 85 ║
║ Pear   ║ 75 ║  0 ║
╚════════╩════╩════╝

Source:

Modified the formula found here:

Explanation:

The core part is the SMALL function

SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15)

It creates an array of row numbers for a specified range, where cells not containing anything (="") have a value that's 100 more than the corresponding row number (+100*). On the other hand, the cells failing the condition (i.e., non-blank cells) will only be assigned their row numbers. This array is passed on to the SMALL function with B$15 as the second argument, which tells it to fetch the kth smallest item in the array.

So, we want to sum the rows where the row starts with the day number, which is what this part does:

1*(ROW($A$2:$A$13)>=SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15))

However, we also want to restrict the totalling up to the cell directly above the next non-blank cell; hence, the < operator and B$15+1 (i.e., (k+1)th smallest item) in the following part:

1*(ROW($A$2:$A$13)<SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15+1))
1
  • Great solution when the merged cells are necessary. Commented Mar 4, 2018 at 17:44
0

If you can unmerge the cells for your Day field, such that the day number appears in every row, a Pivot Table would be by far the easiest way to obtain the result you require:

Pivot

5
  • As @Mahesh mentioned, if the cells in column A can be unmerged, a simple SUMIFS() will work. Fill this right and down from B16: =SUMIFS(C:C,A:A,B$15,B:B,$A16). Commented Mar 4, 2018 at 17:55
  • @Bandersnatch IMO, a Pivot Table is a better solution since the SUMIFS expression would need to be modified for every new day added to the dataset, a Pivot Table would not.
    – Lee Mac
    Commented Mar 4, 2018 at 18:09
  • Not really. The summary table (see OP's expected output) would get a new column for each new day, and the formula gets the day number from the header. I don't really like Pivot tables, mainly because I think they prevent new users from learning how to use formulas. That said, I've been impressed with the quality and thoroughness of your answers outlining how to use them. Commented Mar 4, 2018 at 18:48
  • @Bandersnatch Thank you for your recognition, it makes the time invested constructing the answers all the more worthwhile. Maybe I'm a little biased, as I use Pivot Tables on a daily basis to offer end-users flexibility when viewing database extracts (with automatic refreshing of the content) and so I'm a huge fan of their functionality. Formulae certainly have their place, but I find that their overuse can lead to obfuscated spreadsheets which can become difficult to maintain. My 2p. :)
    – Lee Mac
    Commented Mar 4, 2018 at 19:07
  • Unfortunately, column A is merged, but I appreciate your help nonetheless. I haven't used pivot tables before, but this has opened my eyes to them. Commented Mar 5, 2018 at 12:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .