Extracting HOUR from a timestamp in SUMIFS statement

Hello all,

I have a problem that has been driving me mad - I would be grateful of some help!

I have a spreadsheet where:
Column A contains names (text string)
Coumn B contains costs (numbers)
Colum C contains a timestamp in the format dd/mm/yy hh:mm:ss

I want to be able to create a formula that says "add up all the costs from Column B, where Column A contains 'DAR' within the string, and the hour is less than 12pm regardless of what the day/date is"

I used the following fomula, but I keep getting a 0 returned:

=SUMIFS(B:B,A:A,"*DAR*",C:C,HOUR(C:C)<12)

The dates in the C column are across multiple days, but essentially I am trying to 'ignore' the days and add up all costs that happen before 12pm - irrespective of what day they happen on.

I also tried using a SUMPRODUCT statement, but that equally drove me to the brink of insanity.

I would be most grateful if someone could help. I have a tight deadline coming up, so any help as soon as possible would be much appreciated!

Thanks and kind regards

Darren

try

Formula:

Please Login or Register  to view this content.

the word should be without * * (means full length like DARREN or something like that)

Create helper column D:

D2: =MOD(C2,1) and copy down.

Try this:

=SUMIFS(B:B,A:A,"*DAR*",D:D,"<0.5")

Try

=SUMPRODUCT((B1:B100)*(ISNUMBER(SEARCH("DAR",A1:A100)))*(HOUR(C1:C100)<12))

With SUMPRODUCT use defined ranges rather than whole columns

SUMIF won't work as you need to "separate" out the time from the date: you could add a helper column to do that ..

use

=MOD(C1,1) and format as hh:mm

Then use (with helper in column H)

=SUMIFS(B:B,A:A,"*DAR*",H:H,"<0.5")

Excel time is a number 0 to 1 so 12 midday is 0.5

Hi sandy666 and Phuocam

Thanks so much for your quick replied, however it doesn't quite get to where I need it to be.

Sandy666 - I may be misunderstand what you say above, but I used your formula, it only matches for the exact text string. So if I put in 'Darren' it picks out anything that is equal to 'Darren'. What I need it to do is pick out anything with 'Dar' in it, so if 'Darren' and 'Darwin' were in the list of strings, it would pick those out as matches as they have 'Dar' in them.

Phuocam - your method works a treat, but is there a way to do this without a helper column?

Thanks again

Darren

Use John's SUMPRODUCT formula. It works with * *

Hi John

Thanks for your formula - it works a treat. As a side question, is there any technical reason that I can't use full column references, as opposed to a specific range? The reason I ask is because I have this formula in a 'summary' sheet, but the data is being pulled from a separate sheet. Each month, I will paste new data into the 'data' sheet, but I don't know how many rows will be in it from month to month. There for, I was trying to get around using a specific range.

Thoughts?

Thanks again for your help

Darren