|
Excel SUMIF formula statisfying two conditions
For all rows in other worksheet where column A = X and column B =
Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
=sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000))
the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
My formula looks like this, but It returned with #NUM!.
=SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
The main reason for this is that you have to specify a range when using
SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary and the * multiplier. Try this: =SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2:D100)) HTH, Paul -- "Eva L" wrote in message ... My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
I have modified a bit to satisfy my need, however it returned with zeros when
my input data satisfied the conditions. =SUMPRODUCT(--(Input!$A$2:$A$500=$A6),--(Input!$B$2:$B$500=B$5),(Input!$D$2:$D$500)) If anything could help, I'd appreciate it. Thanks in advance. "PCLIVE" wrote: The main reason for this is that you have to specify a range when using SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary and the * multiplier. Try this: =SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2:D100)) HTH, Paul -- "Eva L" wrote in message ... My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
You might want to check to see if the conditions are, in fact, satisfied.
For example: Are the values Sheet1 column B formatted as dates, or are they just text? Find a row that you think matches the criteria and then test it with a formula. Enter this formula (replace "B2" with the cell reference that you think is a match). =Input!B2=B5 If the two cells are a match, it will return TRUE. If not, it returns FALSE. Do the same thing for column A (replace "A" with the cell reference that you think is a match). =Input!A2=A6 For text fields, check for possible trailing spaces that in your data. HTH, Paul -- "Eva L" wrote in message ... I have modified a bit to satisfy my need, however it returned with zeros when my input data satisfied the conditions. =SUMPRODUCT(--(Input!$A$2:$A$500=$A6),--(Input!$B$2:$B$500=B$5),(Input!$D$2:$D$500)) If anything could help, I'd appreciate it. Thanks in advance. "PCLIVE" wrote: The main reason for this is that you have to specify a range when using SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary and the * multiplier. Try this: =SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2:D100)) HTH, Paul -- "Eva L" wrote in message ... My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
Thank you! And you are right, one of the conditions did fail. Once I fixed
that, it works. You are the best. Thank you so much! "PCLIVE" wrote: You might want to check to see if the conditions are, in fact, satisfied. For example: Are the values Sheet1 column B formatted as dates, or are they just text? Find a row that you think matches the criteria and then test it with a formula. Enter this formula (replace "B2" with the cell reference that you think is a match). =Input!B2=B5 If the two cells are a match, it will return TRUE. If not, it returns FALSE. Do the same thing for column A (replace "A" with the cell reference that you think is a match). =Input!A2=A6 For text fields, check for possible trailing spaces that in your data. HTH, Paul -- "Eva L" wrote in message ... I have modified a bit to satisfy my need, however it returned with zeros when my input data satisfied the conditions. =SUMPRODUCT(--(Input!$A$2:$A$500=$A6),--(Input!$B$2:$B$500=B$5),(Input!$D$2:$D$500)) If anything could help, I'd appreciate it. Thanks in advance. "PCLIVE" wrote: The main reason for this is that you have to specify a range when using SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary and the * multiplier. Try this: =SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2:D100)) HTH, Paul -- "Eva L" wrote in message ... My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
You're welcome!
-- "Eva L" wrote in message ... Thank you! And you are right, one of the conditions did fail. Once I fixed that, it works. You are the best. Thank you so much! "PCLIVE" wrote: You might want to check to see if the conditions are, in fact, satisfied. For example: Are the values Sheet1 column B formatted as dates, or are they just text? Find a row that you think matches the criteria and then test it with a formula. Enter this formula (replace "B2" with the cell reference that you think is a match). =Input!B2=B5 If the two cells are a match, it will return TRUE. If not, it returns FALSE. Do the same thing for column A (replace "A" with the cell reference that you think is a match). =Input!A2=A6 For text fields, check for possible trailing spaces that in your data. HTH, Paul -- "Eva L" wrote in message ... I have modified a bit to satisfy my need, however it returned with zeros when my input data satisfied the conditions. =SUMPRODUCT(--(Input!$A$2:$A$500=$A6),--(Input!$B$2:$B$500=B$5),(Input!$D$2:$D$500)) If anything could help, I'd appreciate it. Thanks in advance. "PCLIVE" wrote: The main reason for this is that you have to specify a range when using SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary and the * multiplier. Try this: =SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2:D100)) HTH, Paul -- "Eva L" wrote in message ... My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
Excel SUMIF formula statisfying two conditions
you can not use the entire column in sumproduct functions, use a defined
range eg a2:a65530 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: My formula looks like this, but It returned with #NUM!. =SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D:D)) Please let me know if there's any other ways. Thanks! "Marcelo" wrote: =sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000)) the same concept for the second sheet. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Eva L" escreveu: For all rows in other worksheet where column A = X and column B = Y, sum column C. I have tried numerous ways to do this, for instance the other posts' formulas, however it was still not successful. Any other suggestions? Sheet 1 Column 1 Column 2 Column 3 Title Date Amount B Jan-08 10.00 A Feb-08 15.00 A Jan-08 25.00 B Jan-08 10.00 Sheet 2 Column 1 Column 2 Column 3 Column 4 Title Jan-08 Feb-08 Mar-08 A B Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet 1 = Date in sheet 2, then sum amount in sheet 1. |
| All times are GMT +1. The time now is 01:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com