# Combined vlookup and sumifs in a single formula

1. ## Combined vlookup and sumifs in a single formula

Hello everyone,

I just want to introduce myself. I am a new member of this forum. Being an intermediate Excel user, I joined this forum to hopefully learn from all the posts and improve my skills. Also I'm working on a major project right now involving lots of data management and analysis and I could really use some help on a formula involving vlookup and sumifs functions that I've been trying to create unsuccessfully.

I have attached an spreadsheet example and I'm hoping if someone in the forum can help me.

So from the spreadsheet, this is what I'm trying to do.
1. in cell C3, I need a formula that will lookup date 1/25/2012 (in cell B3) in range H2:J7. I want this vlookup to do an exact match and an approximate match as well. So in this example, I want vlookup to return dates 1/25/2012 and 1/26/2012.

2. Then, I want the formula to sum up the Amounts associated with Billed Item X. Those amounts should be associated with the dates returned in number 1 above. So the values should be 1,000 for 1/25/2012 and 500 for 1/26/2012. Therefore, if written correctly, I expect this formula to return a value of 1,500 in cell C3.

3. Then I can just copy that formula for the rest of column C and adjust the parameter so that it would work for column D.

2. ## Re: Combined vlookup and sumifs in a single formula

Try:

=SUMPRODUCT(--(TEXT(\$I\$3:\$I\$7,"mmmyy")=TEXT(\$B3,"mmmyy")),--(\$H\$3:\$H\$7=LEFT(C\$2)),\$J\$3:\$J\$7)

copied down and to next column (D2 should say "Y Total Amount")

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1