Sum Criteria

[Annette]

I used the following:
=SUMPRODUCT(--(Sheet1!B:B="WARRANT"),--(Sheet1!F:F="E"),--Sheet1!C:C)

.... if there is WARRANT in cell in Col B and Col F contains an "E", then add
all the sums in column B that match these specifications ... but it is not
adding up my amounts in col C ... am I using the wrong formula?

 

[RagDyeR]

SumProduct does *not* work with entire column (B:B, F:F) references!

Revise to B1:B65000 if necessary.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I used the following:
=SUMPRODUCT(--(Sheet1!B:B="WARRANT"),--(Sheet1!F:F="E"),--Sheet1!C:C)

.... if there is WARRANT in cell in Col B and Col F contains an "E", then add
all the sums in column B that match these specifications ... but it is not
adding up my amounts in col C ... am I using the wrong formula?

 

[JE McGimpsey]

First, you can't use entire columns in array formulae (which SUMPRODUCT
is, even if you don't have to use CTRL-SHIFT-ENTER). Second, the "--" is
only necessary to convert boolean values to numeric (see

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation). Third, I assume you really meant "add all the sums
in column C", not B. Try something like:

=SUMPRODUCT(--(Sheet1!B1:B1000="WARRANT"),--(Sheet1!F1:F1000="E"),
Sheet1!C1:C1000)

 

[Annette]

Thanks for the two quick responses ... I have corrected the problem with
your assistance.