SUMIFS referencing antother sheet (column) and referencing multiple entries in same column

digital472

Hello - I am beating my head against a rock at the moment. I am trying to use SUMIFS and reference 2 columns in a different sheet. The first is a column that gives a status on "lead type" and has multiple values I want to select (e.g. Prospect, Qualified, Technical Validation...), and the second column has values ($$$) on the Total Contract Value of that lead, and I just want to select the whole column.

What I am trying to do is create a formula that says, "take all of the entries in the sheet that are "lead type" Prospect, Qualified, Technical Validation, ect. in Column A, and their respective Total Contract Values in Column B, and give me the SUM of those Column B values".

I got this to work for a single "lead type" by using this formula: =SUMIF({AAA BD Range 1}, "Prospect", {AAA BD Range 6})

In the above the "Range 1" is the Column A and the "Range 2" is the Column B. I tried to make a formula like this, but it did not work: =SUMIFS({AAA BD Range 1}, "Prospect", "Qualified, "Technical Validation", {AAA BD Range 6}). It just resulted in #UNPARSEABLE

Please help!

Zachary Hall

The syntax on your SUMIFS() formula isn't quite right. You actually have to define the range for each variable and the range at the beginning is what you want to add up. For instance:

• =SUMIFS({AAA BD Range 6},{AAA BD Range 1}, "Prospect",{AAA BD Range 1}, "Qualified,{AAA BD Range 1}, "Technical Validation")
• This assumes {AAA BD Range 6} contains the values you are wanting to get the Sum for.

digital472

Thank you for your help! So this kinda worked in that I no longer have an error, but I am getting $0.00 which is incorrect. Here is the formula that I used: =SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, "Prospect", {AAA BD Range 1}, "Qualified", {AAA BD Range 1}, "Technical Validation")

Thoughts?

I did notice that in one of my early attempts, I made this formula that resulted in $0.00: =SUMIF({AAA BD Range 6}, "Prospect", {AAA BD Range 1}). But if I reversed the entries, it worked. Could that be what is needed above?

KPH

Hi @digital472

The formula @Zachary Hall gave you "assumes {AAA BD Range 6} contains the values you are wanting to get the Sum for."

Without seeing your sheet it is not clear which column is which. You also refer to this column as Total Contract Values, also Column B, and Range 2. If the formula isn't working, you are probably correct in thinking {AAA BD Range 6} is not the right reference to use in this case.

The syntax for the part of the SUMIF formula after the ( is as follows

The thing to sum goes first - this is the column with the $ in

Then a comma

Then the first column to check (lead type)

Then a comma

Then the value to look for (prospect) in that first column

and this repeats for each column or thing to look for.

So the column references Zach used works if AAA BD Range 6 contains the $

 =SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, "Prospect", {AAA BD Range 1}, "Qualified", {AAA BD Range 1}, "Technical Validation")

If the $ are in a different column you will need to reference a different column here (change the part in bold). You may also need to change the other references is these are completely switched around.

If you enter the formula and click on any part of it a box appears that highlights where you are:

Here is the range - the numbers to add up

If I move the cursor the next part is highlighted

This is the range for the criterion - the column to look in

Moving along further

This is the criterion - the thing to look for in the column specified in criterion_range1

Zachary Hall

@digital472

@KPH was 100% correct and did a great job explaining. If you are still getting errors, don't hesitate to let us know!

digital472

Thank you so much for the explanation! This was very helpful! I am not sure what I am doing wrong :( I can only get it to work when I reference a singe "lead type". Here is an example:

=SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, "Technical Validation")

So the above works! I get a dollar value SUM. This is great! But then when I add in another "lead type" like this:

=SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, "Technical Validation", {AAA BD Range 1}, "Business Validation")

Then it breaks, and I get $0.00 as the resulting SUM :(

Here is what the column for my Lead Type/Stage looks like. It is a dropdown list column with selectable values. This is the {AAA BD Range 1}

Here is the what the column for Total Lead Value looks like. I do have some blank cells. Maybe that is it? It is a text/number column. This is the {AAA BD Range 6}

I walked through step by step what you did above, and even saw how it highlights the range and criterion. Is there anything that sticks out that could be the issue?

Thanks again for the help!

KPH

Hi @digital472

This formula:

=SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, "Technical Validation", {AAA BD Range 1}, "Business Validation")

Is calculating the sum of Range 6 where Range 1 is "Technical Validation" and Range 1 is "Business Validation".

Each criteria must be true for it to calculate. If you want the sum where Range 1 is "Technical Validation" or Range 1 is "Business Validation", then you need a slightly different formula. You need to combine SUMIFS with OR like this:

=SUMIFS({AAA BD Range 6}, {AAA BD Range 1}, OR(@cell = "Prospect", @cell = "Qualified"))

This starts the same, with the thing to count, then contains the range for the criterion, but instead of the criterion being the value to look for, you replace it with an OR function (shown in bold).

This OR says the value can be Prospect or Qualified:

OR(@cell = "Prospect", @cell = "Qualified")

If you want to add more possibilities, you do this like this:

OR(@cell = "Prospect", @cell = "Qualified", @cell="Booking")