Help with Sumif formula please

[jedibrown]

Hi all - I am having some problems with a formula.

I am trying to find a formula which picks out the relevant I.D's in the first column and produces how many hours in total it's been worked on if it's with a certain team.

The ID will appear several times in column a and will have hours for each time it appears in column D.

ID Date changed Hours With Total Time

I want a formula which says:

Look in column A (ID) for a specific ID (E.G. 444) then look in column E to see if it's with a specific team (say team A) and if it is, add up all of the hours listed in column D which correspond.

I hope this makes sense - any help will be much appreciated.

 

[SteveO59L]

If you have excel2007 or later, have a look at sumifs()

 

[VoG]

Try

=SUMPRODUCT(--(A1:A100=444),--(E1:E100="A"),D1:D100)

 

[jedibrown]

Hi thanks - unfortunately this doesn't appear to work.

Any other suggestions would be gratefully received.

Thanks

 

[VoG]

In what way does it not work?

 

[jedibrown]

It just comes up with #N/A when I put it in.

Thanks

 

[SteveO59L]

Is your 444 a text value or a numeric?

 

[VoG]

It will return #N/A if your data contains #N/A errors.

Working here

Excel Workbook
	A	B	C	D	E	F
1	111			1	A	8
2	444			2	A
3	333			3	B
4	444			4	B
5	111			5	A
6	444			6	A
Sheet3

 

[jedibrown]

It's a numeric value I think - it's just the number which relates to the ID.

 

[jedibrown]

Does it matter that the numbers to sum are in time format?