Lookup and SUMProduct or SUMIFS?

[gino59]

Hi all, I have the following formula which returns a sum from a table using two dates. I'm trying to add in an extra condition which is to sum between those two dates if the value in a cell equals a set value.

The formula is:
=sumproduct((mData[DATE]>=datevalue(L57))*(mData[DATE]<=datevalue(L58))*(mData[DEBIT]))

I'm trying to figure out how to add the new criteria which is looking in the same mData table [TRANS] column. In other words, look for the value in cell L59 which for example is Acct1xyz and sum all values from the debit column where the TRANS column = Acct1xyz and the dates are between the values in cell L57 and L58.

Clear as mud, huh? I hope someone can help!!!

Many, many thanks!
Gino

 

[gino59]

Perfect! That works great! I guess I better learn more about ISNUMBER - it's not the first time that's tripped me up. Does it have to do with the combobox filling the value in L54? I guess I see ISNUMBER and figure it doesn't apply to a text value!

And yet again - thank you so much! Shorter, faster formulas and code... now that's the ticket!

Cheers!
Gino

 

[Aladin Akyurek]

Perfect! That works great! I guess I better learn more about ISNUMBER - it's not the first time that's tripped me up. Does it have to do with the combobox filling the value in L54? I guess I see ISNUMBER and figure it doesn't apply to a text value!

And yet again - thank you so much! Shorter, faster formulas and code... now that's the ticket!

Cheers!
Gino

You are welcome.


SEARCH returns either a position indicating integer (a hit) or a #VALUE! error ( a miss). ISMUNBER gives TRUE for a hit and FALSE for a miss.

 

[gino59]

Ok - good to know! Always something new to learn!

Cheers,
Gino