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.

Attached Files

Demo 07032024.xlsx (11.3 KB, 3 views)

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

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,

Dear Pocoflyer,

It is solved. I got the result. Thanks

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

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

Attached Files

Demo - sum based on criteria 080324.xlsx (12.1 KB, 3 views)

Is it like this

Demo - sum based on criteria 080324_HK.xlsx

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 ?

See this workbook

Attached Files

Demo - sum based on criteria 080324_HK2.xlsx (27.6 KB, 8 views)

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,

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}.

Thanks xor,

It works perfectly.

Regards,

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.

Attached Files

Sum based on criteria Demo 250324.xlsx (269.4 KB, 3 views)

try:

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}