vlookup help please

[stevetton]

hello

I am trying to make a v look up or index or something whatever works..
one sheet i have a list of stuff with dates, code and description etc
on another sheet i have balances
What i want to do is to enter into a particular cell.. i want it to look up a month ie april.. and then look up another column with a list view with for example 001 in it and return the total of rows on the sheet that is within the date range and has the 001 in it from another monetary column
does this make sense?

[BadlySpelledBuoy]

Post some sample data (in a workbook) and an expected result or two and it will make a whole heap more sense!

Sounds like COUNTIFS is your friend here though.

BSB

[stevetton]

accounting trial one.xlsx

herewith thanks

[Jacc]

Something like this in C2:

Please Login or Register  to view this content.

That date && thing may not work properly as it depends on regional settings. I don't know how to test it on someone elses region.

[stevetton]

it worked for the data that was already entered in the spreadsheet but when I added further funds and amounts and dates.. it did not work for some reason? do i need to adjust any of the variables sorry for sounding so thick!

[stevetton]

accounting trial one.xlsxnew excel herewith

[cbatrody]

Hi,

You need to adjust the data range in the formula to cover the source data. May be you can keep some buffer if you do not wish to change it frequently (like the example given below):

=SUMPRODUCT((MONTH(Expenditure!$A$2:$A$1000)=MONTH(DATEVALUE(1&C$1&2015)))*(Expenditure!$B$2:$B$1000=$A2)*(Expenditure!$F$2:$F$1000))

[JohnTopley]

Extended range (in red) to whatever maximum you need ...(number of rows of data)

=SUMPRODUCT((MONTH(Expenditure!$A$2:$A$100)=MONTH(DATEVALUE(1&C$1&2015)))*(Expenditure!$B$2:$B$100=$A2)*(Expenditure!$F$2:$F$100))

from the original supplied

=SUMPRODUCT((MONTH(Expenditure!$A$2:$A$4)=MONTH(DATEVALUE(1&C$1&2015)))*(Expenditure!$B$2:$B$4=$A2)*(Expenditure!$F$2:$F$4))

[stevetton]

Thank you SO much.. that was really appreciated everyone..