Lookup and SUMProduct or SUMIFS?

gino59

Active Member
Joined
Jul 26, 2010
Messages
496
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try to avoiid * in SumProduct...

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

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

The latter should be faster...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top