SumProduct

[wal50]

Thank you all for your help. The following statement solves sums what I need
on the relevant conditions and ends days of struggle.

=SUMPRODUCT(--($F$2:$F$85="no")*($A$2:$A$85="Region")*($E$2:$E$85=DATE(2004,11,6))*($J$2:$J$85))


The remaining question is, what do I have to do to get a count if the items
involved?

Thanks again

 

[Harald Staff]

Try replacing
$J$2:$J$85
with
1

HTH. Best wishes Harald

 

[Jack Sheet]

Just exclude the "*($J$2:$J$85)" part to get the count of items, ie
=SUMPRODUCT(--($F$2:$F$85="no")*($A$2:$A$85="Region")*($E$2:$E$85=DATE(2004,11,6)))--Return email address is not as DEEP as it appears"wal50" <[email protected]> wrote in messageThank you all for your help. The following statement solves sums what Ineed> on the relevant conditions and ends days of struggle.>>=SUMPRODUCT(--($F$2:$F$85="no")*($A$2:$A$85="Region")*($E$2:$E$85=DATE(2004,11,6))*($J$2:$J$85))>>> The remaining question is, what do I have to do to get a count if theitems> involved?>> Thanks again>

 

[KL]

It is the same formula, but just removing the range being summed from it,
like this:

=SUMPRODUCT(--($F$2:$F$85="no")*($A$2:$A$85="Region")*($E$2:$E$85=DATE(2004,11,6))

and BTW if you use SUMPRODUCT((condition1)*(condition2)*...) construct,
there is no need for double dash "--". As Frank explained below it is needed
when doing this: SUMPRODUCT(--(condition1),--(condition2),...) i.e. when
using commas instead of asteriscs. Thus your formula can look like this:

=SUMPRODUCT(($F$2:$F$85="no")*($A$2:$A$85="Region")*($E$2:$E$85=DATE(2004,11,6))

or like this:

=SUMPRODUCT(--($F$2:$F$85="no"),--($A$2:$A$85="Region"),--($E$2:$E$85=DATE(2004,11,6))

Regards,
KL

 

[JE McGimpsey]

You seem to have things a bit confused.

The -- is only needed to convert a boolean array to a numeric array, as
explained at

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

Using the * operator multiplies the arrays (implicitly coercing them to
numeric) before they are ever seen by SUMPRODUCT. When you do this, you
don't need to add the --

To count the rows that meet the criteria, my preferred way (it's
marginally more efficient):

=SUMPRODUCT(--($F$2:$F$85="no"), --($A$2:$A$85="Region"),
--($E$2:$E$85=DATE(2004,11,6))

alternatively:

=SUMPRODUCT(($F$2:$F$85="no") * ($A$2:$A$85="Region") *
($E$2:$E$85=DATE(2004,11,6))