Sumif with sumproduct? maybe? 2

[eti3nne]

I have the following data:

A1:Limit B1:Time c1:Value D1:Factor
A2:5:51 4:07 46 0
4:22 66 1
A4:total 4:37 86 1
A5: ?370 4:52 103 1
5:07 115 1
5:22 124 0
5:37 131 0
5:52 135 1
6:07 137 1
6:22 138 1
6:37 137 1
6:52 135 1
7:07 131 0
7:22 126 0
7:37 120 0
7:52 114 1
8:07 106 1

I enter a 'time limit eg 5:52 and need the 'total' of 'value' up to that 'time', but only if Factor is 1.

I currently have :
'total' = SUMIF($B2:$b18,"<=" & A2,c2:c18)....

.....but how do I include column D (Factor) in a formula to achieve my aim? (Total should be 370)

Any ideas? Thanks for looking.

 

[GlennUK]

What version of Excel do you have? If you have Excel 2007 then SUMIFS should do it.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

... otherwise, it's this:

=SUMPRODUCT((B2:B18<=A2)*(D2:D18=1)*(C2:C18))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Glenn,
SUMIFS!!!! That's awesome!
I give you a star. I've been SUMPRODUCTing away for so long it never occurred to me they would invent a function that would do what, you know, every wants!
Nice one.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

Hi Scott,

thanks for the star!

Yes, it was a pleasure to find out that they'd included some very useful functions in Excel 2007. Other ones to explore are:
•IFERROR
•AVERAGEIF
•AVERAGEIFS
•COUNTIFS

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Glenn,
Nice. I had quickly discovered the COUNTIFS (after your SUMIFS) but IFERROR, I suspect that does what I hope it does. Makes eliminating those unsightly values so much easier.
Cheers!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

Hi SCott,

IFERROR is very useful. It makes formulas like:
=IF(ISERROR(VLOOKUP( myval, mytable, 2, FALSE )),"not found", VLOOKUP( myval, mytable, 2, FALSE ))

... be able to be written as :

=IFERROR(VLOOKUP( myval, mytable, 2, FALSE ),"not found" )



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Yeah, that is spectacular!
-S

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."