sumproduct multiple criteria using Excel 2003

[Dave69rock]

Hi All

I've attached a sheet to try explain what i'm looking for can't seem to make it work, i'm not sure if an old Version can do it.

Cheers
Dave

[Pete_UK]

You can use this formula:

=SUMPRODUCT((C8:C14=D2)*(G8:G14=D3)*(B8:B14=D4)*(E8:E14=D5),F8:F14)

Note that you need to change the entry in D4 to Y.T.D. (i.e. full-stop at the end).

Hope this helps.

Pete

[Dave69rock]

How Do i add column A to this Formula

[NeedForExcel]

Like this -

Assuming D1 has the Value of Col A

=SUMPRODUCT((A8:A14=D1)*(C8:C14=D2)*(G8:G14=D3)*(B8:B14=D4)*(E8:E14=D5),F8:F14)

[Dave69rock]

It doesn't Seem to work when you add one one Criteria

[NeedForExcel]

See the file attached..

As pete suggested, did you add the full stop?

[JohnTopley]

As per post #4 if D1 = P1 you will get 5 for Jim

[samba_ravi]

=SUMPRODUCT((C8:C14=D2)*(G8:G14=D3)*((A8:A14=D4)+(B8:B14=D4)>0)*(E8:E14=D5)*(F8:F14))
Try this
Your Period Criteria was Y.T.D but in your data table it is showing like Y.T.D.
Please change it

[hlhans]

Hi there,

based upon the most recent uploaded XLS I've set up this furmula in order to rpovide both, a) the minutes depending on team member and coach and b) the minutes just depending on the coach in case "sometimes there won't be a team Member selected in D2 Just a Coach" as mentioned in the description of the problem:
=+IF(ISBLANK(D2);SUMPRODUCT((A8:A14=D1)*(G8:G14=D3)*(B8:B14=D4)*(E8:E14=D5);F8:F14);SUMPRODUCT((A8:A14=D1)*(C8:C14=D2)*(G8:G14=D3)*(B8:B14=D4)*(E8:E14=D5);F8:F14))

What do you think?

Kind regards from Hamburg
Henning