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

 

[Aladin Akyurek]

Try to avoiid * in SumProduct...

=SUMPRODUCT
    mData[DEBIT],
    --(mData[DATE]>=DATEVALUE(L57)),
    --(mData[DATE]<=DATEVALUE(L58)),
    --(mData[TRANS]=L59))

=SUMIFS(
    mData[DEBIT],
    mData[DATE],">="&DATEVALUE(L57),
    mData[DATE],"<="&DATEVALUE(L58),
    mData[TRANS],"="&L59)

The latter should be faster...

 

[gino59]

Thanks, Aladin - as always - I am grateful for your help!!

Worked like a charm (the sumifs) and a quicker formula is always preferred!!!

Many, many thanks!!

Gino

 

[gino59]

As usual, I find something amiss after I get it to work!!

Just curious... is there a way to either...

use SUMIFS for multiple columns? Using the sumifs formula you provided, I tried to use =sumifs(mData[[Debit]:[Deposit]],.... but get a #VALUE error. I don't think SUMIFS likes trying to sum multiple columns.

or is there a way to use a wildcard, say...

=IF(L59="* INC"),sumifs(mData[Deposit]..., sumifs(mData[debit]...

Basically, depending on the text value in cell L59, either perform the SUMIFS on the deposit column or the debit column.


Thank you so much!!
Cheers,
Gino

 

[Aladin Akyurek]

How do we know when Deposit and when Debit?

 

[gino59]

If the [TRANS] cell has the word INC in it (could be AXinc, 245INC, etc. - but will ALWAYS have INC in it) then it's a deposit, everything else is a Debit.

So, I'm looking to throw an IF(L59= *INC...) statement on top of the sumifs. I can use a If it's INC, sumifs the Deposit column, and if it doesn't have INC in the [TRANS] value, the sumifs the Debit column...

???

Thank you!

 

[gino59]

If I use this...

=IF(L54="* INC",SUMIFS(mData[DEPOSIT],mData[DATE],">="&DATEVALUE($L$57),mData[DATE],"<=" & DATEVALUE($L$58),mData[TRANSACTION],"="&$L$54),SUMIFS(mData[DEBIT],mData[DATE],">="&DATEVALUE($L$57),mData[DATE],"<="&DATEVALUE($L$58),mData[TRANSACTION],"="&$L$54))

it returns $0.00

If I use =IF(L54="AXinc",SUMIFs... it returns $2,419.89.

Doesn't seem to like the wildcard. I think I'm using it correctly. One other thought is that the "text" in cell L54 is the link cell for an ActiveX combobox. Would that have something to do with not accepting the wildcard?

Thanks!
Gino

 

[Aladin Akyurek]

IF does not admit wildcards... But that's a lesser issue. What I don't understand is whether you want (a) separate formulas for Debit and Deposit or (b) a single formula that sums both ranges...

 

[gino59]

Thanks, Aladin - sorry for the confusion!

I have a combobox with a sorted/filtered list of transactions (text). I was trying to do a sumif where if the transaction has the word INC in it, sumif the deposit column, if it doesn't have that word in the linked cell, sumif the debit column.

I think I have it working but I'm a bit leery and need to check it out further. This is the formula I came up with...

=IF(ISNUMBER(SEARCH("INC",$L$54)),SUMIFS(mData[DEPOSIT],mData[DATE],">="&DATEVALUE($L$57),mData[DATE],"<=" & DATEVALUE($L$58)+30,mData[TRANSACTION],"="&$L$54),SUMIFS(mData[DEBIT],mData[DATE],">="&DATEVALUE($L$57),mData[DATE],"<="&DATEVALUE($L$58)+30,mData[TRANSACTION],"="&$L$54))

A bit long-winded I'll admit, but it seems to be working. I'm just double-checking the returned sum value to be sure.

Do you think there's a better way?

Many thanks yet again!!
Cheers,
Gino

 

[Aladin Akyurek]

Try...

=SUMIFS(IF(ISNUMBER(SEARCH("INC",$L$54)),mData[DEPOSIT],mData[DEBIT]),mData[DATE],">="&DATEVALUE($L$57),mData[DATE],"<=" & DATEVALUE($L$58)+30,mData[TRANSACTION],"="&$L$54)