• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index With Sum formula

sgmpatnaik

sgmpatnaik

Active Member
Hello Sir,


If we use one ERA Formula with Criteria Formula then how can we add the Sum formula


example


=IFERROR(INDEX(DISP_BRAND,SMALL((DISP_DEAL_NAME="XYZ")*(DISP_BRAND="XYZ")*ROW(DISP_DEAL_NAME),COUNTA(DISP_DEAL_NAME)-COUNTIF(DISP_DEAL_NAME,"XYZ")+ROW(A1))-ROW(DISP_DEAL_NAME)+1),0)


For your Kind knowledge i enclosed my file


https://dl.dropbox.com/u/75654703/KPT%20Accounts.xls


Sir please suggest me for the sum option of the individual Parties which is located in Report button of BP & GP Sheet


HENCE I OBLIGE


WITH REGARDS


PATNAIK
 
SP


Where do you want the formulas on the BP or GP page?


Also can you supply the Worksheet password to unlock it
 
Its ok, I cracked the password


Where do you want the formulas on the BP or GP page?

What are you trying to achieve?
 
Sir in the BP & GP Sheet there are some parties and i want the balance of the Quantity of Brand Wise per suppose the party is G. Lillam Chand Gollechha there is Heading of PPC and OPC show i want the balance of the Brand Wise details how much quantity to supply


I want the sum result in the BP & GP Sheet of Column D Row 8


The Sum Result from the G. Lillam Chand Gollechha Sheet Column I4+j6-h6
 
SP


Do you want the simple formula =I4+j6-h6

or do J6 and H6 need to be the sum of the columns below them ?
 
Thank Q Sir, but i want the sum of the Brand Wise Details i can't get the details in there

so kindly suggest me


Hence i Oblige


With Regards


SP
 
@Hui

Hi!

Cracking workbooks... I now know why your nick... H(acker)u(ser)i(nterface)

Regards!
 
SirJB7,


No I use Elcomsoft's Advanced Office Password Recovery program

The best thing ever invented, since sliced bread
 
@Hui

Hi!

I started in the middle nineties with the AOPB, and just before the millenium I added AOPC.

But in this case... I got afraid with a 37Mb file. Until I found this code within Module4:

-----

[pre]
Code: 
Sub UnhideSheets()
Dim sh As Object
' Loop all sheets in workbook
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
sh.Visible = True
Next sh
End Sub
[/pre]
-----

Regards!
 
SirJB


I have something similar in my personal.xlsb file

But it only shows hidden worksheets, it doesn't unlock them
 
SP


I have been trying to assist you with this problem for two weeks across two posts

You need to assume I know nothing about your spreadsheet or how it works


What are you trying to achieve? and Where are you trying to achieve it

You keep telling me PPC & OPC, that means nothing to me


Do you want the Last value of the Balance Column K on the G. Lillam Chand sheet or the last Balance of the Balance Column K on the G. Lillam Chand sheet when Brand Column L is PPC or OPC? or something else all together?


on the G. Lillam Chand sheet, the last value in Brand with PPC is in cell L649 but there is no corresponding Balance in Column K


If you can specifically say,

I want this value from here;

or the sum of these values from there;

or the sum of these values from here when that column equals some value;

or something else


please tell us
 
At a wild guess

on the BP or GP sheet cell D8 try:
Code: 
=SUMIFS('G. Lillam Chand'!$K$6:$K$1505,'G. Lillam Chand'!$L$6:$L$1505,D$6)

Copy to F8
 
@Hui

Hi!

If you guessed right with that shot in the dark, you should change I.H. by William Frederick Cody.

Regards!
 
Hello Hui Sir,


Good Morning


Sir actually i am facing problem to explain the question what i am thinking in my mind due to the problem of my language which is suspect the problem to under stand any how i want to learn from you that's why again i will try to explain what exact running in my mind


1st Point There is a Gate Way Sheet in that sheet i created some command button's to unhide the hidden sheet which are hidden by the VB Command


2nd point is There is a Button Named with Dispatch Register in the Gate Way Sheet


3rd Point is From Dispatch Register Data is Transferred to Individual Party Sheets


4th Point i don't know what is mistake i made in the Party Sheet But i use the Quantity Columns as ColumnJ4,ColumnJ6:J1505 and ColumnH6:H1505 and for the Brand is ColumnL6:L1505


Now My Request is / Which is running in my mind that is in BP & GP Sheet

In BP & GP Sheet There are the list of Party names and here i want only the Balance Figure of the Party With Brand Wise, How much Quantity we have to Supply the Party (BP is Stand for Bill Pending and GP is Stand for Goods Pending)which is related the Party Columns which i mentioned the above (Sum Range is - ColumnJ4+ColumnJ6:J1505-ColumnH6:H1505,'ColumnL6:L1505)"PPC" & (Sum Range is - ColumnJ4+ColumnJ6:J1505-ColumnH6:H1505,'ColumnL6:L1505)"OPC"


In BP & GP Sheet the Result is to Display in ColumnD8 for PPC and ColumnF8 for OPC


Thanking you


SP
 
SP


Does this sound right


BP or GP Sheet Cell D8

= G Lilliam shand J4 + sum( G Lilliam shand Column J where Column L = "PPC") - sum( G Lilliam shand Column H where Column L = "PPC")


In Excel this is:


Code: 
='G. Lillam Chand'!$J$4+SUMIFS('G. Lillam Chand'!$J$9:$J$1505,'G. Lillam Chand'!$L$9:$L$1505,$D$6)-SUMIFS('G. Lillam Chand'!$H$9:$H$1505,'G. Lillam Chand'!$L$9:$L$1505,$D$6)


But that returns zero?


Please clarify
 
Thank Q Sir let me Check the farmula in other party due to G.Lillam Chand Has No balance for BP or GP, i think That's why it's comes 0
 
Ah Thank Q Hui Sir,


It's Working Great That's Why i am telling you are great and you are great for me

and i am feeling great due i got a teacher like you


Thanks Once Again


With Regards


SP
 
@SirJB7, Missed the formula by "" much

Lucky I never got to buying a lotto ticket
 
You must log in or register to reply here.
Back
Top