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

F

foram

New Member
Joined
May 5, 2020
Messages
11
Platform
  1. Windows
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/TrialExsting/NewIndustryAccount TypeCountryStatusSubscription/TrialGoComet ModulesCustomersSalesOpsCurrencyBudgetedJan/20% of Actual to BudgetedBudgetedFeb/20% of Actual to BudgetedBudgetedMar/20
AurobindoSubscriptionExistingPharmaMONTHLYINDIAACTIVESubscriptionRFQ ContractAurobindoOldUmangINR 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
AurobindoSubscriptionExistingPharmaMONTHLYINDIAACTIVESubscriptionRFQ SpotAurobindoOldUmangINR 85,000
24193.55​
0% 50,000-100% 50,000












Sheet2:
Customer Name + Trial/SubscriptionFor FilterCustomer NameSubscription Periodi.e Start Datei.e. End Datei.e. Total Subscription DaysJanFebMarAprMayJunJulAugSepOctNovDec
AurobindoSubscription15/12/2019AurobindoSAAS-021716th December 2019 to 15th January 2020
16/12/2019​
15/01/2020​
31​
15​
AurobindoSubscription1/16/2020AurobindoSAAS-026216th January 2020 to 15th February 20201/16/20202/15/2020
31​
16​
15​
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
It is not clear from your examples where the expected result comes from, or what $A4, H4, etc refer to in the formula.

Please re-post your samples using XL2BB (green button on the reply toolbar), it would be clearer if you create a new sheet for your sample that only includes the data which is needed for the formula, leaving out any columns which will not be used. For the date section, you can just use 2 columns, i.e. Jan and Feb, instead of the whole year.
 
Upvote 0
Ok, let me retry:.. I have reframed the Sheets to make it easy read:

I already have this formula in Sheet1 for Jan/20 :
=INDEX(Sheet2!$Z:$AH,MATCH($A4,Sheet2!$B:$B,0),MATCH($B4,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))) .. The result answers coming here are 9677.42 & 24193.55 as you see in below table. But I want result answers to be 20000 [i.e. (20000 *15/31)+(20000 *16/31)] & 50000 [i.e. (50000*15/31)+(50000*16/31)]

So,, The above formula mentioned under Column C (i.e. under Jan/20) is partly correct.. Its just that it is NOT Totalling the Sum total of the AurobindoSubscription for the month of Jan/20 w.r.t. its respective GoComet Modules (i.e. either Banana or Apple)
Sheet1:
Customers + Subscription/TrialGoComet ModulesJan/20
AurobindoSubscriptionBanana
9677.42​
AurobindoSubscriptionApple
24193.55​
TOTAL 24,194


Sheet2:
Customer Name + Trial/SubscriptionSubscription Periodi.e Start Datei.e. End Datei.e. Total Subscription DaysJan
AurobindoSubscription16th December 2019 to 15th January 2020
16/12/2019​
15/01/2020​
31​
15​
50,000.00​
20,000.00​
AurobindoSubscription16th January 2020 to 15th February 20201/16/20202/15/2020
31​
16​
50,000.00​
20,000.00​


Please help with the extended corrected formula to get 20000 & 50000 respectively in Jan/20 column in Sheet1 above.

Thanks.
 
Upvote 0
Ok, let me retry:.. I have reframed the Sheets to make it easy read:

I already have this formula in Sheet1 for Jan/20 :
=INDEX(Sheet2!$Z:$AH,MATCH($A4,Sheet2!$B:$B,0),MATCH($B4,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))) .. The result answers coming here are 9677.42 & 24193.55 as you see in below table. But I want result answers to be 20000 [i.e. (20000 *15/31)+(20000 *16/31)] & 50000 [i.e. (50000*15/31)+(50000*16/31)]

So,, The above formula mentioned under Column C (i.e. under Jan/20) is partly correct.. Its just that it is NOT Totalling the Sum total of the AurobindoSubscription for the month of Jan/20 w.r.t. its respective GoComet Modules (i.e. either Banana or Apple)
Sheet1:
Customers + Subscription/TrialGoComet ModulesJan/20
AurobindoSubscriptionBanana9677.42
AurobindoSubscriptionApple24193.55
TOTAL24,194

Sheet2:
Customer Name + Trial/SubscriptionSubscription Periodi.e Start Datei.e. End Datei.e. Total Subscription DaysJan
AurobindoSubscription16th December 2019 to 15th January 202016/12/201915/01/2020311550,000.0020,000.00
AurobindoSubscription16th January 2020 to 15th February 20201/16/20202/15/2020311650,000.0020,000.00

Please help with the extended corrected formula to get 20000 & 50000 respectively in Jan/20 column in Sheet1 above.

Thanks.


foram said:
Ok, let me retry:.. I have reframed the Sheets to make it easy read:

I already have this formula in Sheet1 for Jan/20 :
=INDEX(Sheet2!$Z:$AH,MATCH($A4,Sheet2!$B:$B,0),MATCH($B4,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))) .. The result answers coming here are 9677.42 & 24193.55 as you see in below table. But I want result answers to be 20000 [i.e. (20000 *15/31)+(20000 *16/31)] & 50000 [i.e. (50000*15/31)+(50000*16/31)]

So,, The above formula mentioned under Column C (i.e. under Jan/20) is partly correct.. Its just that it is NOT Totalling the Sum total of the AurobindoSubscription for the month of Jan/20 w.r.t. its respective GoComet Modules (i.e. either Banana or Apple)
Sheet1:
Customers + Subscription/TrialGoComet ModulesJan/20
AurobindoSubscriptionBanana
9677.42​
AurobindoSubscriptionApple
24193.55​
TOTAL 24,194


Sheet2:
Customer Name + Trial/SubscriptionSubscription Periodi.e Start Datei.e. End DateTotal Subscription DaysNo. of Subscription Days in January monthAppleBanana
AurobindoSubscription16th December 2019 to 15th January 2020
16/12/2019​
15/01/2020​
31​
15​
50,000.00​
20,000.00​
AurobindoSubscription16th January 2020 to 15th February 20201/16/20202/15/2020
31​
16​
50,000.00​
20,000.00​


Please help with the extended corrected formula to get 20000 & 50000 respectively in Jan/20 column in Sheet1 above.

Thanks.
Click to expand...
 
Upvote 0
See if this gives you the correct result, it is an array formula, so it must be confirmed with Ctrl Shift Enter, not just enter.

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

I've set the formula to look at rows 2:100, you can adjust this realistically as needed.
 
Upvote 0
Hi, Array is just too complex! Can you please provide a simple better formula than this?

Am yet to try this Array formula as i dont know how to.

Thanks in advance.
 
Upvote 0
Hi, it's not giving proper answer i tried. Am not getting what is this $A4 in this Formula that you gave? what does it suggest when you say =$A4 ? Please help
 
Upvote 0
Hi, it's not giving proper answer i tried. Am not getting what is this $A4 in this Formula that you gave? what does it suggest when you say =$A4 ? Please help



Am not allowed to upload an excel file here for your better reference :(
 
Upvote 0
Click on the cell with the formula, press the F2 key, then hold down the Ctrl and Shift keys and press Enter.

Does that change the result? If not done correctly it will show 0 as the result.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Formula not working
Replies
1
Views
66
RoryA
RoryA
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
498
samide2001
S
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
682
felixstraube
F
W
  • Solved
TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria
Replies
2
Views
717
WhimsicalRaptor87
W
J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
132
Cubist
Cubist

Forum statistics

Threads
1,215,873
Messages
6,127,470
Members
449,384
Latest member
purevega

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top