Prev: Looups and multiple criteria
Next: Sorting with sumif function
From: T. Valko on 15 Feb 2010 12:13
Try this...

Table in the range A2:D9

Lookup values:

F2 = F001
G2 = F020
H2 = F010 (values to exclude)

=SUM(INDEX(B2:D9,MATCH(F2,A2:A9,0),0):INDEX(B2:D9,MATCH(G2,A2:A9,0),0))-SUMPRODUCT((A2:A9=H2)*B2:D9)

--
Biff
Microsoft Excel MVP


"aprendiz" <aprendiz(a)discussions.microsoft.com> wrote in message
news:28B9895E-469C-40F1-83DB-E4C24FDAC4AA(a)microsoft.com...
>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!!


First  |  Prev  | 
Pages: 1 2
Prev: Looups and multiple criteria
Next: Sorting with sumif function