Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I have 2 columns as follow:
Code Amount 1000.200 75.00 5000.100 50.00 5000.200 100.00 5010.200 100.00 5020.200 100.00 5100.100 150.00 5200.100 175.00 5300.200 170.00 and so on I am trying to build a formula that gives me the summary of all the codes that start with 50 and end with 200, the total have to meet the 2 conditions. Your help will be very much appreciated. Fernando |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
in d1 enter:
=SUMPRODUCT(--(LEFT(A1:A8,2)="50"),--(RIGHT(A1:A8,3)="200"),B1:B8) "FGOMEZ" wrote: I have 2 columns as follow: Code Amount 1000.200 75.00 5000.100 50.00 5000.200 100.00 5010.200 100.00 5020.200 100.00 5100.100 150.00 5200.100 175.00 5300.200 170.00 and so on I am trying to build a formula that gives me the summary of all the codes that start with 50 and end with 200, the total have to meet the 2 conditions. Your help will be very much appreciated. Fernando |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I would CONCATENATE those portions of Code (Column A) and Amount (Column B)of
interest in column C using this formula copied down........ =LEFT(A1,2)&"-"&RIGHT(A1,3) Then use a simple SUMIF formula in cell D1 to get the result....... =SUMIF(C:C,"50-200",B:B) Vaya con Dios, Chuck, CABGx3 "FGOMEZ" wrote: I have 2 columns as follow: Code Amount 1000.200 75.00 5000.100 50.00 5000.200 100.00 5010.200 100.00 5020.200 100.00 5100.100 150.00 5200.100 175.00 5300.200 170.00 and so on I am trying to build a formula that gives me the summary of all the codes that start with 50 and end with 200, the total have to meet the 2 conditions. Your help will be very much appreciated. Fernando |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Thanks Jim,
But unfortunately it did not work, as the last 2 zeros are ignored by Excel, I substitute that formula with Mid(A1:A8,6,3)="300" and looks like it could work, but I see -- in your fomula is that a double minus sign or something else (that is what I read). Fernando "Jim May" wrote in message ... in d1 enter: =SUMPRODUCT(--(LEFT(A1:A8,2)="50"),--(RIGHT(A1:A8,3)="200"),B1:B8) "FGOMEZ" wrote: I have 2 columns as follow: Code Amount 1000.200 75.00 5000.100 50.00 5000.200 100.00 5010.200 100.00 5020.200 100.00 5100.100 150.00 5200.100 175.00 5300.200 170.00 and so on I am trying to build a formula that gives me the summary of all the codes that start with 50 and end with 200, the total have to meet the 2 conditions. Your help will be very much appreciated. Fernando |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
If the Code is formatted as text, Jim's solution will work. Using MID is no
different to using RIGHT so I cannot see how one works and the other doesn't. I used the same formula as Jim and it worked OK for me. "FGOMEZ" wrote: Thanks Jim, But unfortunately it did not work, as the last 2 zeros are ignored by Excel, I substitute that formula with Mid(A1:A8,6,3)="300" and looks like it could work, but I see -- in your fomula is that a double minus sign or something else (that is what I read). Fernando "Jim May" wrote in message ... in d1 enter: =SUMPRODUCT(--(LEFT(A1:A8,2)="50"),--(RIGHT(A1:A8,3)="200"),B1:B8) "FGOMEZ" wrote: I have 2 columns as follow: Code Amount 1000.200 75.00 5000.100 50.00 5000.200 100.00 5010.200 100.00 5020.200 100.00 5100.100 150.00 5200.100 175.00 5300.200 170.00 and so on I am trying to build a formula that gives me the summary of all the codes that start with 50 and end with 200, the total have to meet the 2 conditions. Your help will be very much appreciated. Fernando |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |