Search code examples
excelexcel-formulasumifs

Sum multiple values in the same column, if the respective cell below (in the same column) is not empty

I hope someone can help me. I have a table in excel which looks something like this:

A B
1 January
2 Sales 4324
3 Expec. Sales 3324
4
5 February
6 Sales 3423
7 Expec. Sales 2543
8
9 March
10 Sales 5234
11 Expec. Sales 2653
12
13 April
14 Sales 6534
15 Expec. Sales 4453
16
17 May
18 Sales 4356
19 Expec. Sales 4392
20
21 June
22 Sales
23 Expec. Sales 3942
24
25 July
26 Sales
27 Expec. Sales 2942
28
29 August
30 Sales
31 Expec. Sales 4023
32
33 Sum of Sales Sum of sales of months with data
34 Sum Expec. Sales sum of Expec. Sales where Sales in that month has data

The problem is that:

  • the sum sales is only supposed to include the cells, when there is a value in the cells
  • the sum of expected sales should only include months that have already passed

The order of the cells cant change, so the visual style of the table has to be kept the same.

Solution

  • For sum of sales-

    =SUMIFS(B2:B31,A2:A31,"Sales")

    For sum of Expec. Sales-

    =SUMIFS(B2:B31,A2:A31,"Expec. Sales")

    Edit: Use below formula to sum Expec. Sales where there are values on sales.

    =SUMPRODUCT((INDEX(FILTER(A2:B15,A2:A15="Sales"),,2)>0)*(INDEX(FILTER(A2:B15,A2:A15="Expec. Sales"),,2)))