Determine the sum of values based on other values in a different range in Excel

Hello,

Can you help me with an Excel (2007) formula to determine the sum of several values in a particular range when the basis for which values to include is defined by values in a different range?

For example, suppose each cell in the range B3:B11 contains some random value as shown in the first screenshot below.  Furthermore, suppose some of the cells in the adjacent range C3:C11 contain an "x":
 1    1)  What formula would be best to use in cell C2 to display the sum of the column B values for which, in column C, an "x" is present in the corresponding row (i.e. 3 + 4+ 1 +1 = 9)?

    2)  and, so that the formula could be pasted at the top of additional columns to display a similar total for each:
 2
    3)  and finally, so that any other letter or symbols could be used in place of "x" and still give the totals:
 3
I'm pretty sure the answers to 2 & 3 are to: make the column B reference absolute and use ...,<>"",... respectively but I just wanted to include them to be sure.

Thanks EE-20101223.xlsx
Steve_BradyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
Here you go:

for example:
in cell C2 add the following formula and press Ctrl+Shift+Enter:
=SUM(IF((C3:C11<>""),$B$3:$B$11,0))
You can drag to cover the header of the other columns.

EE-20101223-updated.xls
gbanikCommented:
You could use SUMPRODUCT for the purpose. It is not an array formula and hence much faster (easy visible with sizable sets of data).
paste the formula in C2 and drag it into the rest of the row headers
=SUMPRODUCT(($B3:$B11)*(C3:C11<>""))
EE-20101223-1-.xlsx
Rory ArchibaldCommented:
A simple SUMIF will do it:

in C2: =SUMIF(C3:C11,"<>",$B$3:$B$11)
and copy across.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

barry houdiniCommented:
Hello Steve,

The optimal function for a single condition is SUMIF, non-array and more efficeint than SUMPRODUCT. For "x"s this formula in C2 copied across

=SUMIF(C3:C11,"x",$B3:$B11)

for just "not empty" you can use the criteria "<>", i.e.

=SUMIF(C3:C11,"<>",$B3:$B11)

regards, barry
barry houdiniCommented:
......ah, too slow, Merry Christmas Rory......:)
Rory ArchibaldCommented:
Merry Christmas, Barry - I assume the extra ellipses were my present? :)
gbanikCommented:
Yes @Steve_Brady, SUMIF is more efficient then SUMPRODUCT :) (But I guess SUMIF is not available in 2003 though... use SUMIF if you are using 2007 or higher). SUMPRODUCT actually works as an array formula internally though and hence slower.
gbanikCommented:
Merry Christmas to you both - Barry and Rory :)
Rory ArchibaldCommented:
SUMIF is available in all Excel versions, as far back as I remember. (it's SUMIFS that is only available from 2007 on)

Merry Christmas!
Steve_BradyAuthor Commented:
Thanks for the responses.


>>gbanik:
You could use SUMPRODUCT for the purpose.
    =SUMPRODUCT(($B3:$B11)*(C3:C11<>""))


Can you explain the multiplication indicator (*) in this formula?  I looked up =SUMPRODUCT() which has this syntax:

    SUMPRODUCT(array1,array2,array3, ...)

and from the documentation it seems that the multiplication is supposed to be done automatically with the resulting products then being summed.  So why is the * needed in this case?


>>rorya:
A simple SUMIF will do it:
    =SUMIF(C3:C11,"<>",$B$3:$B$11)


The SUMIF formula certainly is the shortest and it works. However, although I understand that <> is "not equal to," I have no clue how the "<>" works as the 2nd argument in this formula.  Please explain.

Nevermind.  Barry gave the explanation:


>>barryhoudini:
for just "not empty" you can use the criteria "<>", i.e.
    =SUMIF(C3:C11,"<>",$B3:$B11)


Thanks for explaining it Barry.  One question it raises though.  For "not empty," I always use <>"" as in "not equal to nothing."  However, does the simple <> work just as well.  In other words, does <> actually mean "not empty" all the time or just in this case?





Rory ArchibaldCommented:
The <> only works without the "" comparison for SUMIF/COUNTIF formulas.
Steve_BradyAuthor Commented:
>>rorya:
The <> only works without the "" comparison for SUMIF/COUNTIF formulas.


Thanks rorya.
Steve_BradyAuthor Commented:
Thanks
barry houdiniCommented:
Hello Steve

Rather than

=SUMPRODUCT(($B3:$B11)*(C3:C11<>""))

some people prefer to use a syntax like this

=SUMPRODUCT(--($B3:$B11),--(C3:C11<>""))

The results will be the same but I believe the second one is very marginally more efficient. In the first one the arrays are multiplied to form one array (of 1/0 values, created by multiplying arrays of TRUE/FALSE values) and SUMPRODUCT operates on that single array (although I think the help on SUMPRODUCT says there must be at least 2 arrays - that's wrong), thereby only summing not productting

With the second syntax there are two arrays of TRUE/FALSE values, explicitly converted by using -- to 1/0 values. SUMPRODUCT then works on the 2 arrays, so the result is the same but the methods are different.

You can use other operators for the explicit conversion from TRUE/FALSE to 1/0. Typically any mathematical operation that doesn't change the value like adding 0 or multiplying by zero. I favour +0, partly because there have been instances in Excel 2007 where using -- in SUMPRODUCT leads to it not updating when values were changed, i.e.

=SUMPRODUCT(($B3:$B11)+0,(C3:C11<>"")+0)

Note that given the fact that in the first formula above the single array only needs to be summed, that version can use SUM rather than SUMPRODUCT, i.e.

=SUM(($B3:$B11)*(C3:C11<>""))

The downside is that you need to "array enter" that version which you don't with the SUMPRODUCT

If you haven't see Bob Phillips write up on SUMPRODUCT its worth reading here

regards, barry

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.