SUMIFS with VLOOKUP

sandy.tiearney64611

On a sheet I call "Project Group Hour Totals", I am trying to add the hours from a sheet I call "Project Time Tracker", using VLOOKUP to find both my Project Category and my Project group.  My Project Time Tracker Range 1  is the hours column on the project time tracker, and Range 2 includes 6 columns.  The project Number being the first and the group being the last.

I've attempted this with SUMIF and AND, which returned an incorrect argument error.

=SUMIF(AND({Project Time Tracker Range 1}, VLOOKUP([Project Category]10, {Project Time Tracker Range 2}, 1, false), VLOOKUP([# of Emp / Group]10, {Project Time Tracker Range 2}, 6, false)))

I've also tried SUMIFS, which returen Unparseable

=SUMIFS({Project Time Tracker Range 1}, (VLOOKUP([Project Category]10, {Project Time Tracker Range 2},1,false),(VLOOKUP([# of Emp / Group]10,{Project Time Tracker Range 2},6,False))

 

 

Kelsey Hayden

Hello,

Thanks for reaching out! It sounds like you are trying to create a formula that will sum the values in the hours column from your sheet "Project Time Tracker” if all the evaluated criteria is true.

If so, then you are correct that using a SUMIFS statement would add the values in the hours range that meet the specified Project Category and Project Group criteria. Since the SUMIFS function sums numbers within a range that meet multiple criteria, you do not need to include the AND or VLOOKUP functions. 

Here is an example of what the formula might look like in your sheet:

=SUMIFS({Project Time Tracker Range 1}, {Project Time Tracker Range 2}, [Project Category]@row, {Project Time Tracker Range 3}, [# of Emp / Group]@row)

In this case, the Project Time Tracker Range 1 is referring to the Hours column (the group of cells to sum), the Project Time Tracker Range 2 is referring to the Project Category column and the Project Time Tracker Range 3 is referring to the Group column in your "Project Time Tracker" sheet. 

Additionally, to possibly improve performance in your sheets, I recommend substituting the row numbers with @row in your cell references. If a row is moved Smartsheet won’t need to modify the cell references, which can result in quicker sheet load and save times. You can also copy and paste this formula without having to manually change the row numbers on cell references, saving you time when you need to copy your formulas. 

For more information on working with the SUMIFS function, I also recommend checking out this article from our Help Center: https://help.smartsheet.com/function/sumifs

Please let me know if you’re looking for something else and I’ll be more than happy to advise further!