find text in column sum different column, value in another sheet

[dad812]

needing a formula to place in 6 cell locations in each of the 12 monthly sheets that is the total for that income.

I have a workbook with 12 worksheets named, "Jan" - "Dec", each sheet has 6 cells for income totals 1-6 i.e. F3 - F8
I keep income in a worksheet named "Income" with 5 columns, example;

Worksheet "Income" row 1 is a header row and has this format
"A" | "B" | "C" | "D" | "E"
Income |Date |From |Deposit To|Amount
--------------------------------------------
RMDs 1/4/2019 Source 1 Bank 1 10.50
RMDs 1/4/2019 Source 2 Bank 2 8.00
RMDs 1/6/2019 Source 1 Bank 1 4.50
DADs 1/8/2019 Source 3 Bank 1 12.10
DADs 1/10/2019 Source 4 Bank 3 2.10
RMDs 2/3/2019 Source 1 Bank 1 9.10
DADs 2/4/2019 Source 3 Bank 1 7.15
Misc 2/4/2019 Source 5 Bank 3 9.50

and so on for each month,

Worksheet "Income" column "E" contains amounts, am needing a formula I can use for the Monthly Sheets Jan - Dec, Income totals 1-6
Jan Income #1 totals cell = F3
Jan Income #2 totals cell = F4
...
Jan Income #6 totals cell = F8
Same income cells for all other month sheets

This had a previous solution, the Income sheet column requirements have changed slightly, previous solution was

Please Login or Register  to view this content.

and I used this formula in income cells 1-6 and only needed to change the last F3 to F4, F5, F6, F7, F8 for the other incomes. this worked for all 12 sheet months. Thanks in advance

[FDibbins]

You say this worked, but don't indicate that it no longer works or what the problem might be? Perhaps you could explain that in more detail?

Also, it is almost always better if all data is on 1 sheet (instead of 12, like you have). I that something you would consider?

Finally, To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[dad812]

FDibbins, thanks for your quick response,

Q1 - You say this worked, but don't indicate that it no longer works or what the problem might be?
Sentence just before Code states - This had a previous solution, the Income sheet column requirements have changed slightly, I changed naming conventions to be consistent throughout the WB

Q2 - Also, it is almost always better if all data is on 1 sheet (instead of 12, like you have)
3rd. sentence - I keep income in a worksheet named "Income" with 5 columns, example;

Q3 - I will be happy to include a sample and will upload soon.

[dad812]

sample WB, Income sheet is populated, only needing the formula for income totals, sheets are protected - NO password. Thank you

[dad812]

In trying for a soultion I know that,

Please Login or Register  to view this content.

will sum ALL of Income using Sheet B3 as a criteria, now needing the other half of the formula. As the second requirement: match current selected sheet - it uses 3 letter abv. of month with month from Income!B:B, that should give me a total for that Income and selected month. Thanks in advance for any assistance

[dad812]

working on a solution, is someone able to provide insight on why this isn't working and help on solution.

Please Login or Register  to view this content.

it should sum Income!E:E where Income!A:A(saved income) = B3(selected income) AND Income!B:B(excel date) = selected sheet month

Thanks in advance

[FDibbins]

That is a really small file with a lot going on in it - care to show where these formulas are going so we dont have to search all over?

[dad812]

In the 12 Monthly Worksheets, Cells F3 - F8

[kvsrinivasamurthy]

Pl note the change in 1st Row of Jan Sheet. Same is required for all sheets.

In F3 of monthly sheet then copied down.

=SUMPRODUCT((Income!$A$2:$A$85=$B3)*(Income!$B$2:$AB$85>=$X$1)*(Income!$B$2:$AB$85<=EOMONTH($X$1,0))*(Income!$E$2:$E$85))

In the file only for Jan formula entered.
Values in B3:B8 should exactly match with the content in Column A of Income Sheet.

[dad812]

thanks kvsrinivasamurthy for the response, however the Income sheet is not static.

1) the remainder of this year has not been entered. so additional rows will be added
2) in previous code example a solution for getting current month as a number is available;

Please Login or Register  to view this content.

I am looking for how to compare the month in Income!B:B(that contains a excel date) to the month number from code above.
again thanks

[kvsrinivasamurthy]

In the formula if dynamic range is required

For
Income!$A$2:$A$85

Change as

Income!$A$2:INDEX(Income!$A$2:$A$85000,COUNTA(Income!$A$2:$A$85000))

It will cover up to the last filled Cell. Similarly for other ranges.

(Income!$B$2:$AB$85>=$X$1)*(Income!$B$2:$AB$85<=EOMONTH($X$1,0))

This portion is to select all dates in the month of that sheet. For Jan it covers from 1/1/19 to 31/1/19.

[dad812]

I am closing this thread, the solution I choose was to add a column on the Income Sheet and store an additional piece of data. all original code remains.