Excel help!

[Oggy997]

Morning all!
I know there are a few excel guru's on here, wondering if anyone could offer assistance!

Column B has a list of job titles (can be renamed, so no set value in any set of rows), names in Column D, hours in Columns EFG
I need to count the instances of names (E) against a certain value in title (B)
eg

A         B         C         D         E         F         G
       Holiday              Dave       7.34
       Holiday              Paul                 7.5
       Holiday              Ian                            5.5
       Holiday
       Holiday
       Holiday

Would give a count of 3, but recalculates if names are added to rows below / names are removed etc.

Hope that makes sense?!? Thanks

 

[Deeg]

Countif

 

[Oggy997]

Maybe i was over-analysing it but couldnt get a countif to work on that....

Used sumproduct to sum the hours in efg but cant count the instances above. Lol

 

[Deeg]

I'm on my phone mate, so not in a great position to help.

I would have thought sumif(s) / countif(s) would work as long as all your values are correct.

 

[Oggy997]

Well countifs is returning #Value (I've never used it before!)

=COUNTIFS('A SHIFT'!B4:B117,"Holiday",'A SHIFT'!E4:G117,">=0.001")

^ Trying to count the instances of "hours"

 

[Oggy997]

Got it!
{=COUNT(IF('A SHIFT'!B4:B117="Holiday",IF('A SHIFT'!F4:J117,0)))}

 

[Deeg]

It's odd that just using countif didn't work.

At least it's sorted though.

 

[C.J]

I have a time sheet that shows each member of staff clocking in, clocking out, their contracted working ours (pulls from a separate sheet using VLOOKUP) and the plus/minus time if they work over/leave early.

What I need is, a way to add up each member of staff's extra time. The staff names aren't pre populated, they sign in as they arrive. So, it would need to look for all 'John Smith's and count their extra hours cell.

I know there's an easy function, but I'm stuck.

 

[SC03OTT]

I think a SumIf formula is what you're needing. You'll want to sum the values in column G where column B contains "John Smith".

 

[C.J]

I think a SumIf formula is what you're needing. You'll want to sum the values in column G where column B contains "John Smith".

Perfect, thanks!