Sum of Multiple Columns with multiple dimensional criteria

[Umar]

Dear All,

I have a query. I am having a database of sales and net credits for one year for thousands of consumers. It is presented as rows for consumer numbers and columns for sales and net credits. Therefore for each month having two columns i.e. Sales, Net Credit. So, it makes up 2 x 12 = 24 columns.

I want to have a formula that identifies " the sum of recoveries for a specific small set of consumers for a period of months, let's say from Feb 16 till May 16."

* A SAMPLE FILE IS ATTACHED *

Waiting for you kind response.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Umar]

Working Perfect. Narayan you are great. Thanks alot.

 

[Naresh Yadav]

Respected Narayan Sir,

Sorry to Bother You again, but kindly suggest what will be the appropriate formula if we keep the same scenario, except the consumer Number and Month range should be only 1(Month>=Range)

I have attached the excel file that will help you to understand
I am trying to solve the query based on your above-given solution but getting failed every time...




regards
Naresh

 

[NARAYANK991]

Hi Naresh ,

See the attached file.

Narayan

 

[bosco_yip]

Try another option,

1] Range B1:M1, fill all blank cells with date

2] In C13, formula copy down :

=SUMIFS(INDEX(B$3:M$7,MATCH(B13,A$3:A$7,0),0),B$1:M$1,">="&D$11,B$1:M$1,"<="&E$11,B$2:M$2,C$11)

Regards
Bosco

 

[Naresh Yadav]

Respected Narayan Sir,

I M not getting the correct result when we change the Month from Jan-16 to Feb-16

 

[Atlas Johnson]

I guess the problem is

1. the Merged cells on Row 1. If you unmerge them
2. Tweak the formula a little to start QTY calculation from Column A not B it seems to work.
3. Play with this part for changing the date criteria if you want($A$7=$A$1:$H$1)

Attached is the sheet.
Cheers,
AJ

 

[NARAYANK991]

Hi Naresh ,

I am not able to understand your problem ; the formulae I have put in are in cells G8 and G9 ; are these two cells showing the wrong results ?

Narayan

 

[Naresh Yadav]

thank you so much Narayan Sir and Bosco Sir, i have got my desired result from your support..

thanks Atlas for your effort and support, but i just wanted a formula that can allow me to get my desired result without unmerging the cell..

regards
Naresh

 

[Naresh Yadav]

Respected Narayan Sir,

Your Formula was correct, perhaps it was my Mistake I couldn't make you clear about my Desire result, now I have got my result based on your initial POST

regards
Naresh