Summing based on two criteria

  • Thread starter Thread starter hossa_dude
  • Start date Start date
H

hossa_dude

Hi Guys...

Here's what I want to do:
I have a list:
Project Number......Person....Date....Working hours
XY0815....Jeff...01/01/04.......5
XY0815...Jeff.....01/02/04......10
AA4711...Jeff13...01/01/04....5
etc.


Now, I want to sum all hours Jeff worked on a spefic project regardles
of the day.....
Any chance how to do that?

Ah...before I forget.....
Any chance in counting the working hours even if only a part of th
number is found? (eg 0815 instead of the full string XY0815)?

Kind regards
Joer
 
Hi

You could use SUMIF for this:
=SUMIF(A2:A15,"XY0815",D2:D15)

For part-projects use:
=SUMIF(A2:A15,"*0815*",D2:D15)
 
Hi Andy, thanks for the response....this, however, doesn't do the trick
as it would only look for the project code.

Probably I forgot to mention, that Jeff is not the only person in th
list.....
Therfore I will have to search by name AND project-code and return th
totals by project code....

Sorry for being to unspecific...:
 
=SUMPRODUCT((A1:A100="XY0815")*(B1:B100="Jeff"),C1:C100)

and

=SUMPRODUCT(--(ISNUMBER(FIND("0815",A1:A100)))*(B1:B100="Jeff"),C1:C100)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry! Misread the post! I'll go home now . . .

--
Andy.


Andy B said:
Hi

You could use SUMIF for this:
=SUMIF(A2:A15,"XY0815",D2:D15)

For part-projects use:
=SUMIF(A2:A15,"*0815*",D2:D15)
Click to expand...
 
Hmm..first one works but second one doesn't...comes back wit
"#n/a".....
Any idea....do i really have to type the "--" in front of "ISNUMBER"
 
Hey dude,

It works for me with my (limited) test data.

The -- is actually not necessary, but it is not the problem. I used it when
building the formula up.

=SUMPRODUCT((ISNUMBER(FIND("0815",A1:A100)))*(B1:B100="Jeff"),C1:C100)

What exactly does your version look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top