How to combined VLOOKUP and SUMIFS

Kamil Ismail

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

Paul Newcome

Are you able to provide some screenshots?

Kamil Ismail

Here I provide you the screenshots

Paul Newcome

A few questions and observations...

 

1. Are you trying to sum dollar amounts or count how many times that particular criteria is met? The syntax for the formula you have in your screenshot is that of a COUNTIFS, but you are using a SUMIFS.

2. What type of data is in {Finance_Master_Loading_Data Range 1}?

3. What type of data is in {Finance_Master_Loading_Data Range 2}?

4. I noticed some inconsistencies in your data. In some rows you have "Site 1" (space) and in other rows your have "Site1" (no space). Unless that is intentional, it will skew your data.