SUMIF with Multiple Conditions

**guilbj2** · 01-04-2007, 01:58 PM

I'm trying to get a sum only if 2 conditions are met.

Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.

What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...

=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))

but it's returning zero values regardless of the criteria being met or not. Any suggestions ?

**VBA Noob** · 01-04-2007, 02:08 PM

Hi,

Try

=SUMPRODUCT(--(TEXT($A$2:$A$10,"dd/mm/yy")="01/02/07")*(--($B$2:$B$10="London")*(TEXT($C$2:$C$10,"hh:mm"))))

VBA Noob

**guilbj2** · 01-04-2007, 02:27 PM

I've tried modifying the formula you provided after changing the A, B, C to the actual ranges and am getting the same result. Do you see any syntax errors that may be causing the problem ?

=SUMPRODUCT(--(TEXT('Jan ''07'!$A$2:$A$750,"dd/mm/yy")="01/01/07")*(--('Jan ''07'!$E$2:$E$750="London")*(TEXT('Jan ''07'!$J$2:$J$750,"hh:mm"))))

**VBA Noob** · 01-04-2007, 02:29 PM

Formula cell Needs to be formatted as [H]:MM

VBA Noob

**guilbj2** · 01-04-2007, 02:30 PM

Yep, it was already formatted that way.

**guilbj2** · 01-04-2007, 02:32 PM

The column with the dates in it is formatted as such, not as text. Could that have an impact ?

**VBA Noob** · 01-04-2007, 02:36 PM

Post an example and I'll have a look

VBA Noob

**guilbj2** · 01-04-2007, 02:54 PM

Please forgive the total newb question... but how do I send you an example? I've tried to attach, but I'm only getting an option for graphics and .txt files.