Multiple conditions

J

jlkirk

Active Member
Joined
May 6, 2002
Messages
328
Office Version
  1. 365
In column A I have various accounts labeled Account A, Account B, Account C, Account D, and Account E, over 250 in number, not in any particular order. In Column B, I have money deposits corresponding to the various accounts listed by dates (the dates in column C). In column D I have text (>50 letters/words) that contain the words "Dividends", "Interest", and "Capital Gains", located at random places within the text.

What I would like to do is list each deposit in each account by the name in column B, by date, in columns D, E, and F. For example, in column D I would like to list the dividends received in account, by date, each dividend deposit, each interest and each capital gains deposit.

Any help?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Something Like this:


Book1
ABCDEFGH
1AccountDepositDateText
2B132024-02-03xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 10.002024-01-28Dividends
3E172024-02-08xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 20.002024-01-31Interest
4A172024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 12.002024-01-31Capital Gains
5E152024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 17.002024-02-04Interest
6D112024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqA$ 20.002024-02-11Dividends
7D102024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 14.002024-02-12Capital Gains
8C202024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 19.002024-02-13Dividends
9D192024-02-06xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 17.002024-01-31Dividends
10A202024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqB$ 13.002024-02-03Interest
11E142024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqB$ 17.002024-02-07Dividends
12D102024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 14.002024-02-10Dividends
13B142024-02-10xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqC$ 18.002024-01-26Interest
14A142024-02-12xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 20.002024-02-07Interest
15A202024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqC$ 19.002024-02-07Capital Gains
16C192024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
17C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
18C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqD$ 11.002024-02-04Dividends
19A192024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-05Interest
20A102024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 15.002024-02-05Capital Gains
21B172024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 19.002024-02-06Capital Gains
22B172024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-13Capital Gains
23E172024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 15.002024-01-28Interest
24C182024-01-26xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqE$ 14.002024-01-28Interest
25D152024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-08Interest
26A122024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-11Capital Gains
Sheet11
Cell Formulas
RangeFormula
E2:H26E2=LET(Data1,$A$2:$C$26,Desc,$D$2:$D$26,Type,IFS(ISNUMBER(SEARCH("Interest",Desc)),"Interest",ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),NewData,HSTACK(Data1,Type),AcctName,CHOOSECOLS(NewData,1),TranDate,CHOOSECOLS(NewData,3),SORTBY(NewData,AcctName,1,TranDate,1))
Dynamic array formulas.
 
Upvote 0
awoohaw said:
Something Like this:


Book1
ABCDEFGH
1AccountDepositDateText
2B132024-02-03xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 10.002024-01-28Dividends
3E172024-02-08xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 20.002024-01-31Interest
4A172024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 12.002024-01-31Capital Gains
5E152024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 17.002024-02-04Interest
6D112024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqA$ 20.002024-02-11Dividends
7D102024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 14.002024-02-12Capital Gains
8C202024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 19.002024-02-13Dividends
9D192024-02-06xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 17.002024-01-31Dividends
10A202024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqB$ 13.002024-02-03Interest
11E142024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqB$ 17.002024-02-07Dividends
12D102024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 14.002024-02-10Dividends
13B142024-02-10xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqC$ 18.002024-01-26Interest
14A142024-02-12xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 20.002024-02-07Interest
15A202024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqC$ 19.002024-02-07Capital Gains
16C192024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
17C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
18C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqD$ 11.002024-02-04Dividends
19A192024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-05Interest
20A102024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 15.002024-02-05Capital Gains
21B172024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 19.002024-02-06Capital Gains
22B172024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-13Capital Gains
23E172024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 15.002024-01-28Interest
24C182024-01-26xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqE$ 14.002024-01-28Interest
25D152024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-08Interest
26A122024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-11Capital Gains
Sheet11
Cell Formulas
RangeFormula
E2:H26E2=LET(Data1,$A$2:$C$26,Desc,$D$2:$D$26,Type,IFS(ISNUMBER(SEARCH("Interest",Desc)),"Interest",ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),NewData,HSTACK(Data1,Type),AcctName,CHOOSECOLS(NewData,1),TranDate,CHOOSECOLS(NewData,3),SORTBY(NewData,AcctName,1,TranDate,1))
Dynamic array formulas.
Click to expand...
Excellent! Works Perfectly; thanks
 
Upvote 0
it is sorted in the LET() function. You can change that in ways you like. I thought I sorted it as you asked in the OP.
 
Upvote 0
awoohaw said:
it is sorted in the LET() function. You can change that in ways you like. I thought I sorted it as you asked in the OP.
Click to expand...
You did, absolutely. Its just that I might want to sort differently in the future
 
Upvote 0
Then I suggest using a helper column and using the regular AUTO-FILTER tool
This would be the formula for the helper column.
Excel Formula: 
=LET(
Desc,$D$2:$D$26,
Type,
IFS(
ISNUMBER(SEARCH("Interest",Desc)),"Interest",
ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",
ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",
1,""),
Type)

NOTE: I'm free forming this, so it may need tweaking.
 
Upvote 0
awoohaw said:
Then I suggest using a helper column and using the regular AUTO-FILTER tool
This would be the formula for the helper column.
Excel Formula: 
=LET(
Data1,$A$2:$C$26,
Desc,$D$2:$D$26,
Type,
IFS(
ISNUMBER(SEARCH("Interest",Desc)),"Interest",
ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",
ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),
Type)

NOTE: I'm free forming this, so it may need tweaking.
Click to expand...
Thanks again...Ill let you know
 
Upvote 0
jlkirk said:
Thanks again...Ill let you know
Click to expand...
works great.

Let me give you another: Suppose I want to match the account in column A with the text in column D. For example, I want column E to list the Dividends from Account A on any particular date. I think it has to do with the SUMIFS function with an accompanying AND function, but I cant figure out how to parse out the matching language (in this case "Dividend") from column D.

Here is as far as I got: Column E=Sumifs(And(A:A="Account A"),(D:D="*Dividends*"),B:B), but it doesnt seem to work.
Any ideas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Splitting text into only two cells ?
Replies
4
Views
140
Peter_SSs
Peter_SSs
J
  • Question
Sumif vs Sumproduct
2
Replies
11
Views
217
etaf
etaf
JohnZ1156
  • Solved
SUMIFS with multiple criteria
2
Replies
17
Views
395
KRice
K
himderfella
  • Solved
Do not calculate formula if there are blank cells
Replies
4
Views
537
MARK858
MARK858
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
240
bobaol
B

Forum statistics

Threads
1,215,106
Messages
6,123,124
Members
449,097
Latest member
mlckr

Share this page

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