From: aprendiz on
I have an spreadsheet with columns like:

col A col D col E colF
B230 8 1 0
F001 -5 0 10
F002 3 20 0
F010 5 -29 3
F014 40 3 7
F020 -25 -6 -100
F113 0 0 0
H002 34 -50 23
in ascendent order.
I want to be able to Sum col D to colF if colA is between F001 and F020
except F010.
I am just starting with this and I really don't know how to mix and match
all the formulas. any help will be much appreaciated!!
From: Teethless mama on
=SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8)


"aprendiz" wrote:

> I have an spreadsheet with columns like:
>
> col A col D col E colF
> B230 8 1 0
> F001 -5 0 10
> F002 3 20 0
> F010 5 -29 3
> F014 40 3 7
> F020 -25 -6 -100
> F113 0 0 0
> H002 34 -50 23
> in ascendent order.
> I want to be able to Sum col D to colF if colA is between F001 and F020
> except F010.
> I am just starting with this and I really don't know how to mix and match
> all the formulas. any help will be much appreaciated!!
From: Teethless mama on
Try this one:

=SUMPRODUCT((LEFT(A1:A8)="F")*(RIGHT(A1:A8,3)+0>=1)*(RIGHT(A1:A8,3)+0<=20)*(RIGHT(A1:A8,3)+0<>10)*D1:F8)


"Teethless mama" wrote:

> =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8)
>
>
> "aprendiz" wrote:
>
> > I have an spreadsheet with columns like:
> >
> > col A col D col E colF
> > B230 8 1 0
> > F001 -5 0 10
> > F002 3 20 0
> > F010 5 -29 3
> > F014 40 3 7
> > F020 -25 -6 -100
> > F113 0 0 0
> > H002 34 -50 23
> > in ascendent order.
> > I want to be able to Sum col D to colF if colA is between F001 and F020
> > except F010.
> > I am just starting with this and I really don't know how to mix and match
> > all the formulas. any help will be much appreaciated!!
From: Glenn on
Looks like you missed a couple of things:


colA is **between** F001 and F020

and

**except** F010


Teethless mama wrote:
> =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8)
>
>
> "aprendiz" wrote:
>
>> I have an spreadsheet with columns like:
>>
>> col A col D col E colF
>> B230 8 1 0
>> F001 -5 0 10
>> F002 3 20 0
>> F010 5 -29 3
>> F014 40 3 7
>> F020 -25 -6 -100
>> F113 0 0 0
>> H002 34 -50 23
>> in ascendent order.
>> I want to be able to Sum col D to colF if colA is between F001 and F020
>> except F010.
>> I am just starting with this and I really don't know how to mix and match
>> all the formulas. any help will be much appreaciated!!
From: Glenn on
And I see you realized that as I was typing my previous post. Your new solution
is very similar to the one I posted to one of the OP's other identical requests.

Glenn wrote:
> Looks like you missed a couple of things:
>
>
> colA is **between** F001 and F020
>
> and
>
> **except** F010
>
>
> Teethless mama wrote:
>> =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8)
>>
>>
>> "aprendiz" wrote:
>>
>>> I have an spreadsheet with columns like:
>>>
>>> col A col D col E colF
>>> B230 8 1 0
>>> F001 -5 0 10
>>> F002 3 20 0
>>> F010 5 -29 3
>>> F014 40 3 7
>>> F020 -25 -6 -100
>>> F113 0 0 0
>>> H002 34 -50 23
>>> in ascendent order.
>>> I want to be able to Sum col D to colF if colA is between F001 and
>>> F020 except F010.
>>> I am just starting with this and I really don't know how to mix and
>>> match all the formulas. any help will be much appreaciated!!