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>

 

[Legacy 167133]

@ lundbhaiz,

Thank you for the clear explanation.

 

[Faysamantha]

Thank you all very much for your help, however there is just one little problem. the spreadsheet is on a much larger scale and the figures/values are not bundled in columns next to eachother, they have columns hidden inbetween. i should have said this at the beginning, sorry. here are the actual cell co-ordinates:

	C	I	N	W
2	Debt Today =	£1300
6	Date	Balance	Loan	Credit Card
285	17/07/2012	£1200	-£500	-£2000
286	18/07/2012	£1800	-£480	-£2105
287	19/07/2012	£2200	-£460	-£2500

<tbody>
</tbody>

 

[lundbhaiz]

what is there in all these hidden columns ? and is row 6 the headers for your data ? what is there in row 1,3,4,5 and 7 ? how many rows of data you have ?

 

[Faysamantha]

in the hidden columns are formulas to get the values in the above cells. row 6 is the headers, the other rows which are hidden have all the previous dates up to 200 odd days ago.
my dates currently go to 31/12/2015 which is row 1545

 

[Robert Mika]

Perhaps:
C2:
-SUM(A4:C4)
Add to this cells:
A3:
Balance
B3:
Loan
C3:
Credit
Then in A4 drag accross to C4:
Assuming that the headers name are unique:

Excel 03:
=IF(ISNA(MATCH(TODAY(),$A$7:$A$2000,0)),"",INDEX($B$7:$Z$2000,MATCH(TODAY(),$A$7:$A$2000,0),MATCH(A3,$B$6:$Z$6,0)))
Excel 07/10
=IFERROR(INDEX($B$7:$Z$2000,MATCH(TODAY(),$A$7:$A$2000,0),MATCH(A3,$B$6:$Z$6,0)),"")

 

[lundbhaiz]

try this formula. there is still an easy way to do it but i only know limited so here my formula:- =SUMIF($C$7:$C$1545,TODAY(),$I$7:$I$1545)+SUMIF($C$7:$C$1545,TODAY(),$N$7:$N$1545)+SUMIF($C$7:$C$1545,TODAY(),$W$7:$W$1545)

 

[Peter_SSs]

Does this do what is required?

=SUMPRODUCT(VLOOKUP(TODAY(),C7:W2000,{7,12,21},0))

 

[lundbhaiz]

Does this do what is required?

=SUMPRODUCT(VLOOKUP(TODAY(),C7:W2000,{7,12,21},0))

fantastic formula but you did not show me way to speed up my macro, peters. oh just joking. it seem difficult so i understand so no worry.

 

[Faysamantha]

PERFECT!!!! Thanks Peter_SSs

you all helped a lot! thank you