Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MNSNOWGAL
 
Posts: n/a
Default 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!!
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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!!



  #3   Report Post  
Trevor Shuttleworth
 
Posts: n/a
Default

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!!



  #4   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

  #5   Report Post  
MNSNOWGAL
 
Posts: n/a
Default

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!!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to return cell contents based on multiple conditions Bill Excel Worksheet Functions 3 January 19th 05 10:59 AM
How do I calculate interest and deduct late fees based on date pa. leon New Users to Excel 1 January 6th 05 03:09 PM
Count Based upon Multiple Conditions hkslater Excel Worksheet Functions 4 November 19th 04 05:43 AM
Create a total based on multiple conditions is not giving correct. Jacob Excel Worksheet Functions 2 November 4th 04 05:07 AM
SUMIF based on 2 conditions TimH Excel Worksheet Functions 3 October 28th 04 08:18 PM


All times are GMT +1. The time now is 04:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"