How to combined VLOOKUP and SUMIFS

Kamil Ismail
Kamil Ismail ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi everyone,

I encounter one issue creating formula and linking between two different sheets. I don't know which formula to use but let me share with you what I have done and if you all have a solution to my problem, that would be great.

I'm working on a financing sheet, it will be a lot about proposed cost, final cost, site and activity cost.

  1. The first worksheet is about Finance Master Loading Data - this is where the client insert the site location, activity cost, original cost baseline, final cost baseline
  2. In site location we have site 1, site 2, site 3, site 4, site 5, site 6, site 7 and it continue
  3. Activity Cost is where cost of doing the activity. e.g.: HVAC, Security, MISC 
  4. The column in the worksheet is "Site", "Activity Cost", "Original Cost Baseline" and "Final Cost Baseline"
  5. Another worksheet is where we compile this according the "Activity Cost".
  6. In the second worksheet, what i'll do is in the "HVAC" column is put this formula:
  7. =SUMIFS({Finance_Master_Loading_Data Range 2}, {Finance_Master_Loading_Data Range 3}, "Site 1", {Finance_Master_Loading_Data Range 6}, "HVAC")
  8. This formula is to calculate cost on each "site" that do "HVAC" activity.
  9. {Finance_Master_Loading_Data Range 2} => Final Cost Baseline.
  10. {Finance_Master_Loading_Data Range 3}, "Site 1" => the site.
  11. {Finance_Master_Loading_Data Range 6}, "HVAC" => Activity Cost
  12. And it return value of 0.00, why???
  13. I have tried COUNTIFS, the result still return to 0.00

Please, I need your help to this matter. Let me know if you need further clarifications.

Thank you


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!