SOLVED

# Vlookup and Sum Help

Occasional Contributor

# Vlookup and Sum Help

Can someone help me out with a forumla?

I'm trying to enter an item # on the Lookup sheet of the attached sample and have the YTD Forecast cell in B2 return the sum of the YTD Totals from the Planning Sheet for the matching item # and the forecast rows.

For example, item # 1234 shows up in 3 locations and I need to sum the forecast numbers for those locations.  The total returned should be 2694.  The YTD column for the forecast row is conditional formatted to green if that helps.

6 Replies
best response confirmed by RHughes11569 (Occasional Contributor)
Solution

# Re: Vlookup and Sum Help

@RHughes11569 Try this in B2 on "Lookup"

``=SUMIFS(Planning!D2:D46,Planning!A2:A46,A2,Planning!C2:C46,"Forecast")``

# Re: Vlookup and Sum Help

``=SUMIFS(Planning!\$D\$2:\$D\$46,Planning!\$A\$2:\$A\$46,A2,Planning!\$C\$2:\$C\$46,"Forecast")``

# Re: Vlookup and Sum Help

Perhaps PivotTable could be considered as an option

# Re: Vlookup and Sum Help

@Riny_van_Eekelen That works for me!  I was able to copy your formula into my sheet and then massage it a little to get it to work for my specific worksheet.  Thanks!

# Re: Vlookup and Sum Help

@Sergei Baklan Not that I know a ton about Pivot Tables yet either, but I'm trying to make this as plug & play as possible because some of the people that will be using it know very little about Excel.  I appreciate you helping out though.  I'm definitely learning as I go, so pivot tables are on my list.

# Re: Vlookup and Sum Help

The minus of PivotTable is that user shall to refresh it after updating the data, formulas do recalculation automatically. At the same time that's quite powerful tool and this option better to keep in mind. Everything depends on concrete situation.