Amend formula?

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

Hi all,

I have the following formula which works well:

SUMPRODUCT((Main!$G$4:$G$2885 = "I")*(Main!T4:T2885))

but i want to amend it slightly to check if column B equals GL as well -
think that is: (Main!$B$4:$B$2885 = "GL")

but i don't know how!!

Thanks,

Kirstie
 
SUMPRODUCT works by multiply numbers (row-by-row) and then adding those
individual products up. Your numbers can be actual values and/or logical
expressions. The reason your formula works is because the logical expression

(Main!$G$4:$G$2885 = "I")

evaluates to either TRUE or FALSE which, when used in a mathematical
expression (your multiplication for example), is converted to 1 or 0 in
order to be used in that calculation. So, if the value the logical
expression is multiplied by is TRUE, the rest of the values in the
mathematical expression get multiplied by 1, which keeps them in the final
summation that SUMPRODUCT performs; and, if FALSE, the rest of the values in
the mathematical expression get multiplied by 0, the product of which is 0,
which effectively removes them from the final summation that SUMPRODUCT
performs. In effect, those logical expressions act as IF-THEN filters. So,
if you want another IF-THEN type test, just multiply its logical expression
with the rest of the items being multiplied. Hence, you want this...

=SUMPRODUCT((Main!$B$4:$B$2885 = "GL")*(Main!$G$4:$G$2885 =
"I")*(Main!T4:T2885))

Rick
 
Thanks for that......and i really appreciate the explanation for sumproduct
too. i managed to do it with help from the group, but was never very sure
WHY it worked.

great!

Kirstie
 
See inline comments...
Thanks for that......
Click to expand...

You are most welcome.
and i really appreciate the explanation for sumproduct too. i managed to
do it with help from the group, but was never very sure WHY it worked.
Click to expand...

I figured that out from your question; hence, my brief explanation for how
SUMPRODUCT worked. For more details on this remarkably useful and flexible
function, see here...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Also, this one for an explanation of array functions (what is ulitmately
behind how the SUMPRODUCT function works)...

http://www.cpearson.com/excel/ArrayFormulas.aspx

Rick
 
Back
Top