Sumproduct formula in Excel 2

[Epsilon101]

Hi,

I am trying to do a formula to work out the result of the example below, its proving a little difficult..

          A           B           C           D
    User         location       Group       Minutes
1   Bill            7             3           10
2   Alison         THP            3           54
3   Bob            GEN            2           23
4   Charles        TEL            P           14
5   Bill            7             3           32

Trying to do a formula with sumproduct and sumif to little effect no point in even posting it cos its just so bad

As an example
I want to SUM the minutes part but the criteria isnt just 1 thing its all the columns here, so IF its bill and location 7 and group 3 then sum the minutes.


---------------------------------------

Neil

 

[xlbo]

=SUMPRODUCT((A1:A5="Bill")*(B1:B5=7)*(C1:C5=3)*(D15))

and have a look in the FAQs section as BlueDragon has posted a FAQ there on SUMPRODUCT formulae

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Zathras]

There comes a point with complex criteria (or very large databases) that the DATABASE technique may be a better fit. Take a look and decide for yourself:

Create a range name "database" for your data. With the sample above that would be for the range A16.

Set up your criteria and give the range the name "criteria"
In this case the criteria range would look like this:
[tt]
User location Group
Bill 7 3
[/tt]
and the range I used is F1:H2.

You can actually use any names for the ranges that you want. "Database" and "Criteria" are handy and descriptive, but not required as such.

Then this formula gives you your sum:

=DSUM(database,"Minutes",criteria)

Try it. You might like it.

 

[Epsilon101]

Thanks for this guyz, has been a great help, thanks for your explanation on DSUM zathras, never knew you could do that, it will come in very handy.

---------------------------------------

Neil