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" wrote in message
...
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" wrote in message
...
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")*(C 2: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 ,
"Trevor Shuttleworth" 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" wrote in message
...
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!!