CODEWITHSUNDEEP
excel
JustAnotherDeveloper
JustAnotherDeveloper

Reputation: 3199

Summing a column in Excel based on a another value falling between each rows 2 adjacent cells

I have an excel spreadsheet that will do analysis on direct debits on a selection of accounts, I have a lookup sheet "Sheet2" as shown in the picture which will contain the info I need.

The table I am interested in has 3 columns i am interested in, amount and a to and from date.

On Sheet1 B13 I want to do a =sum() on Sheet2:D5:D:100, but only add it to the total IF the date in B1 is in the range.

I can sum the columns fine, and even sum one row based on the date using -sumifs(), but don't know where to start with this issue. I assume I would need to use a count() or something?

I'm not necessarily looking for a straight answer, but a rough explanation to the autocalc so I can do this sort of thing easily in the future!

enter image description here

Upvotes: 0

Views: 3488

Answers (3)

Wolfgang Kuehn
Wolfgang Kuehn

Reputation: 12926

Plain old sumifs?

    A       B           C
1       2013-03-14  
2   6   2013-01-01  2013-01-02
3   8   2013-03-12  2013-03-23
4   5   2013-05-21  2013-06-01
    =SUMIFS(A2:A4;B2:B4;CONCATENATE("<=";B1);C2:C4;CONCATENATE(">=";B1))

Upvotes: 2

Danzomida
Danzomida

Reputation: 505

I think you'd be best off using an array formula mixed with your sum. Something like

=SUM((B1>Sheet2!$D$5:$D$100)*(B1<Sheet2!$E$5:$E$100)*Sheet2!$C$5:$C$100)

Now what is going on here is happening in several parts:

  1. The first set of inner brackets (B1>Sheet2!$D$5:$D$100) checks that that date in B1 is greater than the first date in your other sheet. This evaluates as 1 for true, 0 for false.
  2. The second set of inner brackets (B1
  3. These 1 or 0 values multiplied are only a 1 if both conditions are true. So the third part comes in to play when the dates align, and the sum is calculated.
  4. This formula is an array formula, since each of these operations are applied to every row within your defined ranges

To enter an array formula in Excel you must enter the formula with CTRL + SHIFT + ENTER. Curly brackets { } will appear around the formula. It will look something like

{=SUM((B1>Sheet2!$D$5:$D$100)*(B1<Sheet2!$E$5:$E$100)*Sheet2!$C$5:$C$100)}

if it has worked. Just comment if you can't quite get it and I'll help you out.

Upvotes: 1

Mikeb
Mikeb

Reputation: 6361

Assuming that you have several dates you are looking to run through this sum, (from the first part of C1 there), I would set it up in one of three ways.

Use an extra sheet with extra columns where the dateLowerBound < date < dateUpperBound is calculated for each date you have in sheet1, and using SUMIF with that extra column as the key.

Use DSUM which can support compound criteria but has a syntax that seems to always make me angry for considering using DSUM.

Write a VB macro (assuming that the structure of the sheet is reasonable static, and that there are well defined points where it would make sense to re-calculate a Total, you could hook it to the onChanged event of the date cells and / or to the Sheet2 data table area.

Upvotes: 0

Related Questions