# Vlookup and Sumif Combination

1. ## Vlookup and Sumif Combination

Hi Guys, I am really struggling with what may be a very simple question and was hoping you may be able to help. I am a OK with Excel but this has me stumped.

Here is where i am at:

I have a summary tab on my work book with several detailed tabs supporting it.
The information on the detailed tabs is entered on specified dates.... but not necessarily in chronological order.
The summary tab pulls the data from each supporting sheet and plugs it in to the relevant month

So i have SUMIF looking up a date range (Month 1 less every greater than Month 1) to give the sum within a particular month... in this case July 2011.
=SUMIF(Support!\$C\$15:\$C\$36,">="&DATE(2011,7,1),Support!\$I\$15:\$I\$36)-SUMIF(Support!\$C\$15:\$C\$36,">="&DATE(2011,8,1),Support!\$I\$15:\$I\$36)

I will be linking the date range to a cell on the summary sheet so you can select the dates without having to adjust the formula.

Here is what i want to add:

Rather than just return everything for the month of July, i would like to be able to reduce (use vlookup?) the support tab down to categories (in this case properties) and then SUMIF by date range. Again I would like to link this to an input cell on the summary page so;

Therefore on the summary I can enter in cell A1 the property name.... Say "House 1"
Then I can enter a to (cell A2) and from (Cell A3) date.... Say "1-July-11" to "1-August-11"

So I will get the summary of all the costs for House 1 between those dates.

I really dont know the best way to do this?

Cheers

2. ## Re: Vlookup and Sumif Combination

Hi and welcome to the forum

From the sounds of it, all you need to do is change your sumif() function to a sumifS() function - it allows many criteria to be used. So for instance, you could sum a range between 2 dates AND for a specific category

Give it a try and let me know how you make out?

3. ## Re: Vlookup and Sumif Combination

You got it.... works spot on as follows:

=SUMIFS(Security!I15:I34,Security!C15:C34,Summary!I2,Security!D15:D34,">="&DATE(2011,7,1),Security!D15:D34,"<"&DATE(2011,8,1))

It seems so simple when you know.

The only thing i am now having trouble with is change the date from nominated date to a cell reference so i can easily plug different date range...

I actually want to plug a date in to say cell A2 and have that be the ">=" date and then have the "<" be the next month.

Can you help with that one?

There are currently 1 users browsing this thread. (0 members and 1 guests)