Dynamic Sum-if function

[Brian]

Hi,

I need to make a dynamic sumif function, where the sumrange is dynamic:

Sumif(range, criteria, sumrange)

Date - outcome1 - outcome2 - outcome 3
1 10 20 10
2 12 15 16
2 13 16 18
3 15 20 19
3 8 3 4
4 20 0 8
5 8 9 7

Using the above example, I need to sum all results on a given date.

Thus, I would prefer a formula like: sumif(a:a;"2"; if(5:5="outcome2") ),
which would give the following result: 31 (15 + 16).

 

[Max]

One way

Assuming table as posted is in cols A to D
In say, F2: =SUMIF(A:A,2,OFFSET(A:A,,MATCH("outcome2",1:1,0)-1))

p/s: You may need to change the commas to semicolons to suit your version
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Stefi]

If you make a Data validation list, say in F1 with source range B11, then

=SUMIF(A:A,2,OFFSET(A:A,0,RIGHT(F1)))

Regards,
Stefi

„Brian†ezt írta:

 

[Brian]

Super, thanks for your help

This saved me lots of trouble

 

[Max]

Welcome, glad it worked for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Alojz]

Another option:
assuming ur data are located in range A18, type:
=SUM((A2:A8=2)*INDEX(A28;;MATCH("outcome2";A11;0)))
press ctrl+shift+enter (this is array formula, without shortcut will give
you ERROR)
N.B. delimiters may differ on ur PC