Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
How do I calculate interest and deduct late fees based on date pa. | New Users to Excel | |||
Count Based upon Multiple Conditions | Excel Worksheet Functions | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |