Merge Month function in SUMIFS function

[wincross]

Please have a look at the attached file.
Sheet "FinanceData" column A to I are client data, I have to manipulate column J and K to fetch data to Month tab.
Instead of manipulation in separate columns, I want to include those formulas in Sumifs function in Month tab.
Please help me how to retrieve exact month text like 'Janaury', 'February' etc from date column in FinanceData sheet and replace the below formula.

=SUMIFS(FinancialData!$C$2:$C$33,FinancialData!$K$2:$K$33,$C4)

[shukla.ankur281190]

Try

E4

=SUMIFS(FinancialData!C:C,FinancialData!$E:$E,">="&DATE(2017,ROWS(E$4:E4),1),FinancialData!$E:$E,"<="&EOMONTH(DATE(2017,ROWS(E$4:E4),1),0))

[wincross]

I have updated thread title. Is it fine now?

[sandy666]

btw. Instead of formulas in two J & K columns on Month tab you can use one formula in a single column
if you wish of course

=CHOOSE(MATCH(IF(YEAR(E2)=YEAR(TODAY()),MONTH(E2),0),{0,1,2,3,4,5,6,7,8,9,10,11,12},0),"","January","February","March","April","May","June","July","August","September","October","November","December")

[shukla.ankur281190]

btw. Instead of formulas in two J & K columns on Month tab you can use one formula in a single column
if you wish of course

=CHOOSE(MATCH(IF(YEAR(E2)=YEAR(TODAY()),MONTH(E2),0),{0,1,2,3,4,5,6,7,8,9,10,11,12},0),"","January","February","March","April","May","June","July","August","September","October","November","December")

Sandy I can be very simple.

Try

=TEXT(E2,"MMMM")

[sandy666]

Shukla,
I know but this is something "for future" instead of too many IFs. Not exactly for this case but more general

[wincross]

Thanks Shukla. It works great.
I need one more help. I tried to include another condition in your formula however it shows #value error.

=SUMIFS(FinancialData!C:C,FinancialData!$E:$E,">="&DATE(2017,ROWS(E$4:E4),1),FinancialData!$E:$E,"<="&EOMONTH(DATE(2017,ROWS(E$4:E4),1),INDIRECT("FinancialData!$BB$2:$BB$" &$C$1),1))

Additional conditional - add only if column BB contains 1.

[JohnTopley]

INDIRECT("FinancialData!$BB$2:$BB$?" &$C$1),1)

Not clear what you are trying here ????

[wincross]

John,
Adding data only if certain conditions met.

=SUMIFS(INDIRECT("FinancialData!$C$2:$C$33"),INDIRECT("FinancialData!$B$2:$B$33"),$AB37,INDIRECT("FinancialData!$BB$2:$BB$33"),1)

Here 33 is static so I placed below code in C1 to find last row dynamically, Now C1 will have row number which has last client data (even after addition or deletion of data).

=SUMPRODUCT(MAX((FinancialData!$D:$D<>"")*ROW(FinancialData!$D:$D)))

I have replaced first syntax with this one

=SUMIFS(INDIRECT("FinancialData!$C$2:$C$" &$C$1),INDIRECT("FinancialData!$B$2:$B$" &$C$1),$AB37,INDIRECT("FinancialData!$BB$2:$BB$" &$C$1),1)

Is it clear?

[JohnTopley]

Works fine for me.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JohnTopley]

See next post ...

[wincross]

John,

I have attached a file with required output.

Result in E4 of Sheet1
I want to add column C data (sheet FinancialData) if the month (column E) is January and column L should have '1' (Sum=120)

Result in E5 of Sheet1
I want to add column C data (sheet FinancialData) if the month (column E) is February and column L should have '1' (Sum=18)

[AliGW]

In E4 copied down:

=SUMIFS(FinancialData!C:C,FinancialData!$E:$E,">="&DATE(2017,ROWS(E$4:E4),1),FinancialData!$E:$E,"<="&EOMONTH(DATE(2017,ROWS(E$4:E4),1),0),FinancialData!$L:$L,1)

Just a case of adding another criterion at the end of the SUMIFS statement.

[wincross]

Thank you so much Ali. It works great.
This is what I wanted.

Thanks everybody for looking into this.

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.