If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Sum based on specific condition
Give this a try:
=SUMPRODUCT(--(A1:A9B1:B9),D19) HTH, Elkar "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#12
|
|||
|
|||
Sum based on specific condition
Hi Wendy
This will do the trick: =SUMPRODUCT(--(A1:A9B1:B9),(D19)) HTH Michael M "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#13
|
|||
|
|||
Sum based on specific condition
Try =SUMPRODUCT(--((A1:A9)(B1:B9)),D19)
Hope this helps, Hutch "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#14
|
|||
|
|||
Sum based on specific condition
Try:
=sumproduct(--(A1:A10B1:B10), D110) adjust ranges as needed. "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#15
|
|||
|
|||
Sum based on specific condition
Something like this, in say E1:
=SUMPRODUCT(--(A1:A10B1:B10),D110) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#16
|
|||
|
|||
Sum based on specific condition
=SUMPRODUCT(--(A1:A9B1:B9),(D19))
The first part, --(A1:A9B1:B9), generates a series of 1's and 0's, with the 1's where your condition is satisfied. The second part (D19) is the series of values you want conditionally added. Sumproduct multiplies corresponding components of the two series and adds the results. "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#17
|
|||
|
|||
Sum based on specific condition
Hope this helps:
=SUMPRODUCT(--(A2:A10B2:B10),D210) Adjust to suit, but remember, when making modifications, all the columns have to be the same length when working with SUMPRODUCT. -- RyGuy "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#18
|
|||
|
|||
Sum based on specific condition
"Wendy" wrote:
Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). =sumproduct((D19)*(A1:A9B1:B9)) ----- original posting ----- "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#19
|
|||
|
|||
Sum based on specific condition
=SUMPRODUCT(--(A1:A9B1:B9),D19)
Regards, Stefi „Wendy” ezt *rta: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
#20
|
|||
|
|||
Sum based on specific condition
Hi Wendy,
I think the easiest way would be to put =IF(A1B1,D1,"") in helper column E and drag down to the end of your data, and then sum column E. You can then hide column E or use a different helper column way off to the right somewhere or even on a separate sheet if you wish. HTH Martin "Wendy" wrote: Need help...I am trying to sum only those values in column (D) whose corresponding value in column (A) is greater than the value in column (B). (A) (B) (C) (D) 1. 6,942 292 0.81 5,614.84 2. 317 0 0.92 292.87 3. 6,207 20,374 7.64 47,421.48 4. 417 17,588 5.84 2,435.28 5. 43,921 108,193 1.01 44,525.47 6. 5,974 15,883 3.11 18,579.14 7. 1,834 7,062 1.79 3,283.57 8. 665 733 2.13 1,416.76 9. 21,855 24,149 1.70 37,153.00 Any suggestions? I've been struggling with this for the past few days. -- Wendy |
Thread Tools | |
Display Modes | |
|
|