How do I calculate sum based on 3 conditions?

I've got 3 columns where I want that info to be "true" and if it is, then
calculate the numbers in the 4th columns for those rows that met the 3 sets
of criteria.

So, if 1) client, 2) status, and 3) type meet my criteria, then I want to
sum the numbers in another column.

I'm going around in circles. Can anyone point me in the right direction?

Thanks much!!

=SUMPRODUCT(--(A1:A100="client"),--(B1:B100="status"),--(C1:C100="type"),D1:
D100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MNSNOWGAL" <[email protected]> wrote in message
news:[email protected]...
> I've got 3 columns where I want that info to be "true" and if it is, then
> calculate the numbers in the 4th columns for those rows that met the 3
sets
> of criteria.
>
> So, if 1) client, 2) status, and 3) type meet my criteria, then I want to
> sum the numbers in another column.
>
> I'm going around in circles. Can anyone point me in the right direction?
>
> Thanks much!!

Look at SUMPRODUCT

Something like:

=SUMPRODUCT(--(A2:A68="client")*--(B2:B68="status")*--(C2:C68="type")*--(D2:D68))

Adjust the columns and rows to suit

Regards

Trevor


"MNSNOWGAL" <[email protected]> wrote in message
news:[email protected]...
> I've got 3 columns where I want that info to be "true" and if it is, then
> calculate the numbers in the 4th columns for those rows that met the 3
> sets
> of criteria.
>
> So, if 1) client, 2) status, and 3) type meet my criteria, then I want to
> sum the numbers in another column.
>
> I'm going around in circles. Can anyone point me in the right direction?
>
> Thanks much!!

Note: If you use the * operator to multiply arrays before passing the
resulting array to SUMPRODUCT to sum, the double negatives are not
necessary:

=SUMPRODUCT((A2:A68="client")*(B2:B68="status")*(C2:C68="type")*
(D2:D68 > 8))

OTOH, if you pass the arrays to SUMPRODUCT(), which is slightly more
efficient, you need to coerce the TRUE/FALSE arrays to 1/0 using --


=SUMPRODUCT(--(A2:A68="client"), --(B2:B68="status"), --(C2:C68="type"),
--(D2:D68 > 8))

See

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

for a more detailed explanation.


In article <[email protected]>,
"Trevor Shuttleworth" <[email protected]> wrote:

> Look at SUMPRODUCT
>
> Something like:
>
> =SUMPRODUCT(--(A2:A68="client")*--(B2:B68="status")*--(C2:C68="type")*--(D2:D6
> 8))
>
> Adjust the columns and rows to suit

THANK YOU!! I copied your formula and tailored it; it works as mine did so
it appears there's a problem with the formatting of one of the numbers that
should have been picked up in the total. Why that didn't occur to me earlier
I have no idea. But I'm going to leave figuring out why it isn't picking up
until tomorrow!

Thanks, again - JoAnn

"Trevor Shuttleworth" wrote:

> Look at SUMPRODUCT
>
> Something like:
>
> =SUMPRODUCT(--(A2:A68="client")*--(B2:B68="status")*--(C2:C68="type")*--(D2:D68))
>
> Adjust the columns and rows to suit
>
> Regards
>
> Trevor
>
>
> "MNSNOWGAL" <[email protected]> wrote in message
> news:[email protected]...
> > I've got 3 columns where I want that info to be "true" and if it is, then
> > calculate the numbers in the 4th columns for those rows that met the 3
> > sets
> > of criteria.
> >
> > So, if 1) client, 2) status, and 3) type meet my criteria, then I want to
> > sum the numbers in another column.
> >
> > I'm going around in circles. Can anyone point me in the right direction?
> >
> > Thanks much!!
>
>
>