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

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

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

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

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