Hi,
Need a formula help for in a summary sheet (Column C,H,K) Sum up all values three different ways from various worksheet as per the criteria. The source data for summing looking values contains as a sheets name Jan to June in columns A:C and there headers are named like Date, Name, Amount.
Thanks for the help.
As you have Office 365, I'd advise using PowerQuery for this.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes . it's possible but not effective. Why do you keep data in the separated sheets?
For C and for Usa regional settings
i have to correct June to Jun.
Please Login or Register to view this content.
or you have to create list of sheets.
For the rest it should be also volatile combination but sum of sumifs.
Last edited by BMV; 11-14-2020 at 03:00 AM.
Thanks for the response,
Could you please provide a solution in Power Query, better for me a formula Solution, because i am not having much knowledge about power query.
Keep record data in the same sheet, then no need to do extra work to combine all sheets
Power Query to combine sheet, change file path in blue.
Combine
thenPlease Login or Register to view this content.
C3
=SUMIFS(Combine[Amount],Combine[Name],B3,Combine[Date],A3)
H3
=SUMIFS(Combine[Amount],Combine[Name],B3,Combine[Date],">="&E3,Combine[Date],"<"&EDATE(F3,1))
K3
=SUMIFS(Combine[Amount],Combine[Name],J3)
Many thanks Bo_Ry for your effective Power query and formula solutions.
here is i have trying to workout with indirect formula. some how i am getting seeking answers and Somewhere summing values are different which cells are highlighted in attached sample sheet for your reference.
could someone looking into that part so then can i get additional trick of the solution.
Thanks for king cooperation and help.
The ranges were incorrect:
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Table1&"'!$C$2:$C$12"),INDIRECT("'"&Table1&"'!$B$2:$B$12"),Q3)),"")
Incidentally, the $ inside INDIRECT are irrelevant. They serve no function. Their abssence does not make the formula increment ranges when dragged. I generally leave them out to serve as a visual reminder that they are "different".
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Table1&"'!C2:C12"),INDIRECT("'"&Table1&"'!B2:B12"),Q3)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you Glenn for correcting the formula.
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.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks