Multiple conditions

[jlkirk]

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?

 

[awoohaw]

You have the syntax for SUMIFS incorrect.
But you can really do this just by using the helper column I suggested and the AUTOFILTER tool.

It should be something like this:
=SUMIFS(rangetosum, criteriarange1, criteria1, criteriarange2, criteria2,....)
But, SUMIFS criteria conditions are hard to enter with formulas, so the helper column again would come in handy.

so your formula above should be:
=SumIFS(B:B,A:A,"Account A",HelperColumn,"Dividends")

if you don't want to use the helper column then you'll need to use a big formula inside the SUMIFS, and entering search criteria in data entry query cells (in the example below A28 and C28):

Book1
	A	B	C	D
1	Account	Deposit	Date	Text
2	B	13	2024-02-03	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
3	E	17	2024-02-08	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
4	A	17	2024-02-04	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
5	E	15	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
6	D	11	2024-02-04	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
7	D	10	2024-02-05	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
8	C	20	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
9	D	19	2024-02-06	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
10	A	20	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
11	E	14	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
12	D	10	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
13	B	14	2024-02-10	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
14	A	14	2024-02-12	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
15	A	20	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
16	C	19	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
17	C	16	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
18	C	16	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
19	A	19	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
20	A	10	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
21	B	17	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
22	B	17	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
23	E	17	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
24	C	18	2024-01-26	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
25	D	15	2024-02-05	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
26	A	12	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
27	DataEntry
28	A		Dividends
29	A	20	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
30	A	19	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
31	A	10	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
Sheet4

Cell Formulas
Range	Formula
A29:D31	A29	=LET( Data,$A$2:$D$26, Accts,$A$2:$A$26, Acct,A28, Type,C28, Desc,$D$2:$D$26, Types,IFS( ISNUMBER(SEARCH("Interest",Desc)),"Interest", ISNUMBER(SEARCH("Dividends",Desc)),"Dividends", ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains", 1,""), FILTER(Data,(Acct=Accts)*(Type=Types),"") )
Dynamic array formulas.

 

[awoohaw]

I also should have mentioned the ADVANCED FILTER TOOL on the data bar as well. That essentially does what the formula I gave you.
It takes some practice getting used to it, but it is probably easier than updating formulas every time you want to look at your data differently.
(and you may not need the helper column).

 

[jlkirk]

You have the syntax for SUMIFS incorrect.
But you can really do this just by using the helper column I suggested and the AUTOFILTER tool.

It should be something like this:
=SUMIFS(rangetosum, criteriarange1, criteria1, criteriarange2, criteria2,....)
But, SUMIFS criteria conditions are hard to enter with formulas, so the helper column again would come in handy.

so your formula above should be:
=SumIFS(B:B,A:A,"Account A",HelperColumn,"Dividends")

if you don't want to use the helper column then you'll need to use a big formula inside the SUMIFS, and entering search criteria in data entry query cells (in the example below A28 and C28):

Book1
	A	B	C	D
1	Account	Deposit	Date	Text
2	B	13	2024-02-03	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
3	E	17	2024-02-08	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
4	A	17	2024-02-04	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
5	E	15	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
6	D	11	2024-02-04	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
7	D	10	2024-02-05	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
8	C	20	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
9	D	19	2024-02-06	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
10	A	20	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
11	E	14	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
12	D	10	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
13	B	14	2024-02-10	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
14	A	14	2024-02-12	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
15	A	20	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
16	C	19	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
17	C	16	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
18	C	16	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
19	A	19	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
20	A	10	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
21	B	17	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
22	B	17	2024-02-07	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
23	E	17	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
24	C	18	2024-01-26	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
25	D	15	2024-02-05	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
26	A	12	2024-01-31	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
27	DataEntry
28	A		Dividends
29	A	20	2024-02-11	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
30	A	19	2024-02-13	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
31	A	10	2024-01-28	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
Sheet4

Cell Formulas
Range	Formula
A29:D31	A29	=LET( Data,$A$2:$D$26, Accts,$A$2:$A$26, Acct,A28, Type,C28, Desc,$D$2:$D$26, Types,IFS( ISNUMBER(SEARCH("Interest",Desc)),"Interest", ISNUMBER(SEARCH("Dividends",Desc)),"Dividends", ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains", 1,""), FILTER(Data,(Acct=Accts)*(Type=Types),"") )
Dynamic array formulas.

How about this to capture the dividends deposited into Account A:

=IF(A2<>"Account A",0,SUMIF(D2,"*Dividends*",B2)) in E2 and copied down for every day deposits are made. Column E could then be totaled to come up with the total amount of Dividends deposited into Account A over a period of time. It could then be "filtered" to derive Dividends deposited on individual days.

 

[awoohaw]

Why do all the extra work. A framework to get all the data for you has already been created in the LET function. Just update it as your needs dictate:
Sum of A Dividends are highlighted in GREEN:

Book1
	A	B	C	D
1	Account	Deposit	Date	Text
2	B	13	45325	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
3	E	17	45330	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
4	A	17	45326	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
5	E	15	45319	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
6	D	11	45326	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
7	D	10	45327	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
8	C	20	45329	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
9	D	19	45328	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
10	A	20	45333	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
11	E	14	45319	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
12	D	10	45335	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
13	B	14	45332	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
14	A	14	45334	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
15	A	20	45322	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
16	C	19	45329	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
17	C	16	45335	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
18	C	16	45335	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
19	A	19	45335	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
20	A	10	45319	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
21	B	17	45322	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
22	B	17	45329	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
23	E	17	45333	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
24	C	18	45317	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopq
25	D	15	45327	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
26	A	12	45322	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital Gainslmnopqlmnopq
27	DataEntry
28	A		Dividends
29	A	20	45333	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
30	A	19	45335	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
31	A	10	45319	xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopq
32
33
34	49
Sheet1

Cell Formulas
Range	Formula
A29:D31	A29	=LET( Data,$A$2:$D$26, Accts,$A$2:$A$26, Acct,A28, Type,C28, Desc,$D$2:$D$26, Types,IFS( ISNUMBER(SEARCH("Interest",Desc)),"Interest", ISNUMBER(SEARCH("Dividends",Desc)),"Dividends", ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains", 1,""), FILTER(Data,(Acct=Accts)*(Type=Types),"") )
A34	A34	=LET( Data,$A$2:$D$26, Accts,$A$2:$A$26, Acct,A28, Type,C28, Desc,$D$2:$D$26, Types, IFS( ISNUMBER(SEARCH("Interest",Desc)),"Interest", ISNUMBER(SEARCH("Dividends",Desc)),"Dividends", ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains", 1,""), SUM(TAKE(FILTER(Data,(Acct=Accts)*(Type=Types),""),,2)) )
Dynamic array formulas.