Formula

[FGOMEZ]

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

[Jim May]

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
>
>
>

[CLR]

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
>
>
>

[Jim May]

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
>
>
>

[CLR]

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
>
>
>

[FGOMEZ]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

[Toppers]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
> > >
>
>
>