Forum Discussion
Enter Value With Conditions
Column A lists various investment accounts, Account A, B, C, D & E. Column B lists daily deposits ($$$) into the corresponding accounts. Column C lists the dates of the deposits. Column D lists jumbled text for each day of each deposit, but each such string of text/numbers contains the words "Dividends", "Interest", or "Capital Gains" somewhere within the jumbled text. What I want to do in Column E is list the deposits by day by type by Account. For example, Column E would list the dividends deposited into Account A by day, Column F, the interest, and Column G the Capital Gains.
I tried the Sumifs with an "AND" but it doesnt seem to work
To achieve your goal of listing the deposits by day by type by account in Columns E, F, and G, you can use Excel's SUMIFS function (is one of the possibilities and functions) along with logical conditions to filter the data based on the criteria provided. Here is how you can set up the formulas in Columns E, F, and G:
Assuming your data starts from Row 2 (with headers in Row 1), you can use the following formulas:
In cell E2 (Dividends for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Dividends*"), "")
In cell F2 (Interest for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Interest*"), "")
In cell G2 (Capital Gains for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Capital Gains*"), "")
Drag these formulas across Columns E, F, and G to apply them to the respective types and accounts. Adjust the range ($B$2:$B$100, $A$2:$A$100, $C$2:$C$100, $D$2:$D$100) as needed based on the actual range of your data.
These formulas will sum the deposits in Column B based on the specified criteria in Columns A, C, and D, and return the total deposits of each type for each account for each day. The IFERROR function is used to handle cases where there are no deposits of a particular type on a given day, in which case an empty string ("") is returned. All formulas are untested because there was no example file with data attached or photos with an example.The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
11 Replies
To achieve your goal of listing the deposits by day by type by account in Columns E, F, and G, you can use Excel's SUMIFS function (is one of the possibilities and functions) along with logical conditions to filter the data based on the criteria provided. Here is how you can set up the formulas in Columns E, F, and G:
Assuming your data starts from Row 2 (with headers in Row 1), you can use the following formulas:
In cell E2 (Dividends for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Dividends*"), "")
In cell F2 (Interest for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Interest*"), "")
In cell G2 (Capital Gains for Account A):
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $C$2:$C$100, $C2, $D$2:$D$100, "*Capital Gains*"), "")
Drag these formulas across Columns E, F, and G to apply them to the respective types and accounts. Adjust the range ($B$2:$B$100, $A$2:$A$100, $C$2:$C$100, $D$2:$D$100) as needed based on the actual range of your data.
These formulas will sum the deposits in Column B based on the specified criteria in Columns A, C, and D, and return the total deposits of each type for each account for each day. The IFERROR function is used to handle cases where there are no deposits of a particular type on a given day, in which case an empty string ("") is returned. All formulas are untested because there was no example file with data attached or photos with an example.The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Thank you for your response.
Let me refine my question: what would your solution be if I wanted to match, per deposit, whether it was a dividend and whether it was deposited into Account A. In other words, enter an amount when the deposit is dividends and the deposit is made into Account A. In this case, Im not concerned about an amount over time, but each deposit whether it is a dividend and whether it is deposited into Account A.
Thanks for your help.
To match each deposit to determine if it's a dividend and if it's deposited into Account A, you can use a combination of IF and AND functions. Here's how you can achieve this:
In cell Z2, you can use the following formula:
=IF(AND($A2="Account A", ISNUMBER(SEARCH("Dividends", $D2))), $B2, "")
- It works great...I sent you a follow-up as well.
- Let me change it up on you a little. Suppose I wanted to sum all of the Dividends that were deposited in Account A over a period of time, not just one day into one cell, say Z2?
To sum all dividends deposited in Account A over a period of time and display the total in cell Z2, you can use the SUMIFS function with modified criteria. Here is how you can do it:
=IFERROR(SUMIFS($B$2:$B$100, $A$2:$A$100, "Account A", $D$2:$D$100, "*Dividends*"), "")
This formula sums the deposits from Column B where:
- Column A matches "Account A".
- Column D contains the word "Dividends".
You can place this formula in cell Z2 to calculate the total dividends deposited in Account A over the specified period. Adjust the range ($B$2:$B$100, $A$2:$A$100, $D$2:$D$100) based on the actual range of your data.
If there are no dividends deposited in Account A over the specified period, the formula will return an empty string ("") due to the IFERROR function, ensuring a cleaner output.
