Forum Discussion
Solved
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 jumbl...
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.
NikolinoDE,
This solution works perfectly. Would you mind dissecting the formula and telling me how it works?
Thanks,
Jeff
This solution works perfectly. Would you mind dissecting the formula and telling me how it works?
Thanks,
Jeff
This formula is an Excel formula that uses the IF function along with the AND and SEARCH functions to check multiple conditions and return a value based on those conditions. Here's how it works:
IF: This is the main function used to perform a logical test and return one value if the test is TRUE and another value if the test is FALSE.
AND($A2="Account A", ISNUMBER(SEARCH("Dividends", $D2))): This is the logical test performed by the IF function. It checks two conditions:
$A2="Account A": This checks if the value in cell A2 is "Account A".
ISNUMBER(SEARCH("Dividends", $D2)): This checks if the text "Dividends" is found within the text in cell D2. The SEARCH function returns the position of the text if found, and ISNUMBER checks if it's a number (meaning the text was found).
If both conditions are TRUE, the IF function returns the value $B2, which is the value in cell B2.
If any of the conditions are FALSE, the IF function returns an empty string (""), indicating no value.
So essentially, this formula checks if the value in cell A2 is "Account A" and if the text "Dividends" is found within the text in cell D2. If both conditions are met, it returns the value in cell B2; otherwise, it returns an empty string.