I want "Sum" of the values in this formula that i already have

[foram]

Hi guys, I have already formulated the beow formula in Cell N4 of Sheet1 :

=INDEX(Sheet2!$Z:$AH,MATCH($A4,Sheet2!$B:$B,0),MATCH($H4,Sheet2!$Z$1:$AH$1,0))*INDEX(Sheet2!$J:$U,MATCH(A4,Sheet2!$B:$B,0),1)/(INDEX(Sheet2!$I:$I,MATCH($A4,Sheet2!$B:$B,0)))

But.. My issue is: I want "Sum" Total of all the resultant values that is for Jan month, Feb month, March month , & so on..

Basically, I want to use Sum in the above Formula.

Note: Above Formula is giving Correct result,, except for Sum of the result values in a month. So, I want to Add Sum or any such equivalent formula for the SumTotal of the result values. I am not allowed to attach file here, so am just copy pasting sheets here. I want result figure in Sheet1 under Jan/20 cell N2 wherein the above formula is mentioned, to be amount 20000 ,, instead of 9677.42 .

Thanks in advance the real Excel Experts :

Sheet1:

Customers + Subscription/Trial

Exsting/New

Industry

Account Type

Country

Status

Subscription/Trial

GoComet Modules

Customers

Sales

Ops

Currency

Budgeted

Jan/20

% of Actual to Budgeted

Budgeted

Feb/20

% of Actual to Budgeted

Budgeted

Mar/20

AurobindoSubscription

Existing

Pharma

MONTHLY

INDIA

ACTIVE

Subscription

RFQ Contract

Aurobindo

Old

Umang

INR

20,000

=INDEX(Sheet2!$Z:$AH,MATCH($A4,Sheet2!$B:$B,0),MATCH($H4,Sheet2!$Z$1:$AH$1,0))*INDEX(Sheet2!$J:$U,MATCH(A4,Sheet2!$B:$B,0),1)/(INDEX(Sheet2!$I:$I,MATCH($A4,Sheet2!$B:$B,0))) ​

21%

20,000

-100%

20,000

AurobindoSubscription

Existing

Pharma

MONTHLY

INDIA

ACTIVE

Subscription

RFQ Spot

Aurobindo

Old

Umang

INR

85,000

24193.55​

0%

50,000

-100%

50,000

Sheet2:

Customer Name + Trial/Subscription

For Filter

Customer Name

Subscription Period

i.e Start Date

i.e. End Date

i.e. Total Subscription Days

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

AurobindoSubscription

15/12/2019

Aurobindo

SAAS-0217

16th December 2019 to 15th January 2020

16/12/2019​

15/01/2020​

31​

15​

AurobindoSubscription

1/16/2020

Aurobindo

SAAS-0262

16th January 2020 to 15th February 2020

1/16/2020

2/15/2020

31​

16​

15​

 

[foram]

yes i have understood your Array formula. But my query is : what do you mean by =A$4 in this formula?.. I think there is some part that we are missing out in this formula.. that part is: Full Column A of Sheet1 should match with Sheet2 (i.e. Customers +Trial/Subscription columns should match

 

[jasonb75]

Just realised the same thing after comparing both formulas again in more detail, this should be right.

=SUM(IFERROR((Sheet2!$B$2:$B$100=$A4)*INDEX(Sheet2!$Z$2:$AH$100,0,MATCH($B4,Sheet2!$Z$1:$AH$1,0))*Sheet2!$J$2:$J$100/Sheet2!$I$2:$I$100,0))

With the array, comparing $A4 to Sheet2!$B:$B only needs to be done once, not in each part like you need to with index and match.

 

[foram]

"Ayes i have followed your Array formula. But am not understanding what is this "=A$4" part in your Formula??

See,, to solve this , We need to fulfill 3 conditions: 1) Customer+Trial/Subscription column of Sheet1 should match with Sheet2,, 2) GoCometModules column (i.e. Apple or Banana) should match with Sheet2,, & 3rd condition is to Multiply the Amounts from Sheet2 as per Subscription Days of that particular month & then Divide that amount with Total Subscription Days.. We need to use "Sum" or "Sumifs" function as well while formulating a formula for this because there are Duplicate Customer+Subscription

 

[foram]

did you get my query? Please let me know what is this "=A$4" part in this formula of Array?

Because i think we have forgot to capture this condition as well that Customers+Trial/Subcription column of Sheet1 should match with Sheet2

 

[foram]

?

 

[jasonb75]

It's $A4 not A$4 and it does the same as MATCH($A4,Sheet2!$B:$B,0) in your formula.

The difference between the 2, MATCH only find the first row that is the same as $A4, the array that I used finds all of the rows that are the same.

 

[foram]

Heyy,, thank you thank you thank you. I got it!

Shall get back to you again if stuck .

God Bless!

Good night! It's 12:40 am Night here in India.