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 |
#21
|
|||
|
|||
Sum based on specific condition
one way is to create another column in E and use the following in column E.
=IF(A1B1,D1,0) Your could then sum column E. Tom "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 |
#22
|
|||
|
|||
Sum based on specific condition
With your data starting in cell A1 (adjust formula if needed), I believe this
will work: =SUMPRODUCT(--(A1:A9B1:B9),D19) The function sumproduct multiplies the pairs together and then sums all those products. The first argument evaluates whether the data in A is greater then its partner in B; if so, the result is 1, and if not, the result is 0. The second argument is just the data in D, which then gets multiplied by the 1's and 0's, and then summed. HTH "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 |
#23
|
|||
|
|||
Sum based on specific condition
How about:
=SUMPRODUCT(D210,--(A2:A10B2:B10)) e = "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 |
#24
|
|||
|
|||
Sum based on specific condition
=SUMPRODUCT(--(A1:A9B1:B9),(D19))
Commit with CTRL SHIFT ENTER HTH, Barb Reinhardt "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 |
#25
|
|||
|
|||
Sum based on specific condition
I replied to this a while ago with a longer explanation, but don't see that
post yet. Here's the short version, but I can elaborate if needed. Assuming your data starts in A!, try the following: =SUMPRODUCT(--(A1:A9B1:B9),D19) HTH "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 |
#26
|
|||
|
|||
Sum based on specific condition
I got a notification of a response to this thread, but there's nothing here.
Something very odd is going on. "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 |
#27
|
|||
|
|||
Sum based on specific condition
Try =SUMPRODUCT(--((A1:A9)(B1:B9)),D19)
I posted this yesterday, but don't see it or any other replies, so I am reposting it. 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 |
#28
|
|||
|
|||
Sum based on specific condition
Hi Wendy,
I've copied this table into Excel A1 : E10. I'd add a 6th column (E): with this formula: =IF(B2C2,"x","") It will put an 'x' into any cell where ColumnHeadingA is greater than ColumnHeadingB Then I'd put the following total at the foot of ColumnHeadingD: =SUMIF(F2:F10,"x",E2:E10) I don't usually respond, so I hope this makes sense (and works!) Kind regards Amanda "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 |
#29
|
|||
|
|||
Sum based on specific condition
The "something very odd" which is going on is that Microsoft's web interface
to the newsgroups is broken. You'll see the messages if you use a newsreader or Google's archives. But as all the people who are asking the questions about this problem are the people who are using the Microsoft web interface, they are not seeing the replies to their questions. :-( -- David Biddulph "Barb Reinhardt" wrote in message ... I got a notification of a response to this thread, but there's nothing here. Something very odd is going on. .... |
#30
|
|||
|
|||
Sum based on specific condition
Here's one way
(A) (B) (C) (D) (E) 1 6942 292 0.81 5614.84 =IF(B2C2,E2,"") 2 317 0 0.92 292.87 =IF(B3C3,E3,"") 3 6207 20374 7.64 47421.48 =IF(B4C4,E4,"") 4 417 17588 5.84 2435.28 =IF(B5C5,E5,"") 5 43921 108193 1.01 44525.47 =IF(B6C6,E6,"") 6 5974 15883 3.11 18579.14 =IF(B7C7,E7,"") 7 1834 7062 1.79 3283.57 =IF(B8C8,E8,"") 8 665 733 2.13 1416.76 =IF(B9C9,E9,"") 9 21855 24149 1.7 37153 =IF(B10C10,E10,"") =SUM(F2:F10) "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 | |
|
|