#1
|
|||
|
|||
Sum based on starting no. of codes
Dear Expert,
I'm looking sum for codes starting from 4 & 5 out of several codes. Attached is reference file for your understanding. Thanks in advance. |
#2
|
||||
|
||||
Please explain the logic
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Sum based on starting no. of codes
Dear Pecoflyer,
There are many codes in the working out of which codes starts with 4 series are income and 5 series are expenses. Frequently required sum of all income codes and sum of all expense codes. Though through filter I can get sum of 4 and 5 series, it become manual process and manually I have to keep watch on the total of income and expense which is referred by other dependent working. Hope I could explain the logic. Regards, |
#4
|
|||
|
|||
Dear Pocoflyer,
It is solved. I got the result. Thanks |
#5
|
||||
|
||||
Care to share the solution with the board?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Sum based on starting no. of codes
Dear Pecoflyer,
Though I got the solution, for 4 series and for 5 series I have to plot separate formula to get the result for sum of 4 series and 5 series. 1. Can it be in single formula ? 2. Currently to get the result, I have to put formula in each line in I and J column. Is it possible to select criteria at range level i.e. month range A5:A13, Code range B5:B13, sum table D5:G13 and month criteria H3 to get the sum of 4 & 5 series ? Demo file is attached for your reference. Thanks in advance |
#7
|
|||
|
|||
Sum based on starting no. of codes
|
#8
|
|||
|
|||
Dear xor,
Result is same as was expected. However need 4 & 5 series total in one cell only. Is it possible like ---> (--LEFT($B$26:$B$34)=4 & 5) to combine the result of 4 and 5 series in one formula only rather to put in 2 different formula for 4 & 5 series respectively ? |
#9
|
|||
|
|||
See this workbook
|
#10
|
|||
|
|||
Dear xor,
Thanks. It works perfectly. Was curious to see if any functionality is there in the excel to choose 4 or 5 series in single formula. Thanks a lot. Regards, |
#11
|
|||
|
|||
Another slightly shorter formula is
=SUMPRODUCT(($A$26:$A$34=K$1)*(OR((LEFT($B$26:$B$3 4)={"4""5"})))*$D$26:$G$34) where I guess you must change \ to , (comma). \ is because I use Danish settings. Edit: {"4""5"} here I wrote {"4" backslash "5"} but it was changed when I posted. I think you should use {4,5}. |
#12
|
|||
|
|||
Thanks xor,
It works perfectly. Regards, |
#13
|
|||
|
|||
Sum based on starting no. of codes
Dear xor,
Sorry to bother you again. I am wondering why trail formula is not working on table. Result give count for the total entries in Feb 24 month instead of sum of 4 & 5 series for that month. Is there any reason or am I making any error? Demo file is attached for the reference. Thanks for your cooperation. Last edited by Parle; 03-25-2024 at 05:05 AM. Reason: Attachment is missing |
#14
|
||||
|
||||
try:
Code:
=SUMPRODUCT(($B$4:$B$47880=$E$3)*(((LEFT($A$4:$A$47880)="4")+(LEFT($A$4:$A$47880)="5"))>0)*$C$4:$C$47880) |
#15
|
|||
|
|||
Or
=SUMPRODUCT(($B$4:$B$47880=$E$3)*(LEFT($A$4:$A$478 80)={"4""5"})*$C$4:$C$47880) where {"4""5"} for you should be {"4","5} |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to hide/delete slides based on keyword in a separate text file based on AND|OR condition? | rupd911 | PowerPoint | 0 | 02-22-2021 08:22 AM |
QR codes in Word | lreinhard7 | Word | 1 | 12-19-2019 06:46 AM |
Enter a city based on a short list of zip codes | jcgwilym | Excel | 1 | 04-19-2017 10:53 AM |
Need help with Field Codes | Cosmo | Mail Merge | 25 | 07-30-2015 01:25 PM |
Is it possible to merge the two VBA codes into just one | sam2149 | Excel Programming | 0 | 02-26-2014 05:28 AM |