formula based on date

[Faysamantha]

hi

i have been struggling for a couple of hours now. what i would like is a formula in Cell B1 of my finance spreadsheet which looks for the current date in column a and sum's the values in the corresponding columns (which are also forumlas)

Here is a mini version:

So today the bank balance is £1200, there is £500 outstanding on the loan and the credit card has £2000 on it.

£1200 - £500 - £2000 = £1300 is owed
=B3+C3+D3

	A	B	C	D
1	Debt Today =	£1300
2	Date	Balance	Loan	Credit Card
3	17/07/2012	£1200	-£500	-£2000
4	18/07/2012	£1800	-£480	-£2105
5	19/07/2012	£2200	-£460	-£2500

<tbody>
</tbody>

 

[p45cal]

1. can you show how you get 1300 for today (17th july) from your sample data?
2. Will there only ever be one row per date?

 

[Faysamantha]

hi, i've edited it a little to show the workings.thanks

 

[p45cal]

could you address this too?

2. Will there only ever be one row per date?

 

[Robert Mika]

=SUMPRODUCT(--(A3:A5=TODAY())*B3:D5)
or
array formula(confirm Control+shift+enter)
=SUM(IF(A3:A5=TODAY(),B3:D5))

 

[Faysamantha]

yeah, there will only ever be 1 row per date

 

[p45cal]

then Robert's answer will be fine (though you might want to prefix the whole formula with a negative operator).

 

[Peter_SSs]

For what it's worth, Robert's SUMPRODUCT formula shouldn't need the extra calculations forced by the --
This should do the same job
=SUMPRODUCT((A3:A5=TODAY())*B3:D5)

An alternative is also:
=SUM(INDEX(B3:D5,MATCH(TODAY(),A3:A5,0),0))

 

[Legacy 167133]

@ Peter_SSs,

Hello Peter,

Its been a while since we last had contact.

Nice observation and answers you have made here, but what exactly does the "--" in a Formula do? I have seen it a couple of times now, but have never used it.

Kind Regards

 

[lundbhaiz]

@ Peter_SSs,

Hello Peter,

Its been a while since we last had contact.

Nice observation and answers you have made here, but what exactly does the "--" in a Formula do? I have seen it a couple of times now, but have never used it.

Kind Regards

it is called double unary operator and it forces conversion fo text (which is the return argument for a condition) in to a number because sumproduct only can work with a number or numbers.