Hi Excel Experts,
Below is the example :
2015-16 - Closed - 123 - 100
2015-16 - Closed - 123 -100
2015-16 - Open - 321 - 0
2015-16 - Closed- 124 - 200
2015-16 - Closed -124 - 200
The above data is in excel as data entry now i want to calculate Licence # 123 is Valued for Rs.100 and 124 fo 200 I want to calculate 300 With the Criteria 2015-16 , Closed.
Regards,
Vajeed
Last edited by mvpscm; 07-21-2018 at 08:36 AM. Reason: Thread to be marked as not solved
Welcome to the forum!
I see you have marked this thread as solved, so I assume you need no help. Is this so?
If not, then you will need the SUMIFS function.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
No , Its not working... It is not so simple as you think
Last edited by mvpscm; 07-10-2018 at 08:21 AM.
What does that mean exactly? In what way is it not working? What did you try?
first =concatenate(2015-16&"Closed") and try with Sumif
Attached Excel Sheet FY ready referrence
Thank you. Please explain to us which columns you used for each of the three calculations.
Something like this maybe?
=SUMIFS('MEIS TCL '!J:J,'MEIS TCL '!C:C,Abstract!B5,'MEIS TCL '!K:K,'MEIS TCL '!K2)
I don't understand how you have calculated that final figure in red.
Last edited by AliGW; 07-11-2018 at 03:51 AM.
that i have done manually ,
Now what i need is
2015-16 ( B )
MEIS Lic # ( H )
MEIS Lic Amount ( J )
I want to sum the Unique values from H&J then calculate the amount and replace tthe manual entry
Try this.
You do not want to use whole column references with this. If you need to make room for future data make the ranges slightly larger than what you expect to use.
Formula:
Please Login or Register to view this content.
Dave
You could also try array entering this formula.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:Please Login or Register to view this content.
Good , But what if I want to select the hole coloumn
Why do you need to?
If you use tables instead of ranges, you won't need to worry about changing formulae when ranges grow.
One good reason NOT to use whole ranges is that it can and often does slow down the calculation speeds of your workbook, introducing lag.
In your other two SUMIF formulas you can reference whole columns with little impact on performance.
It's when you introduce math operations to the whole columns that problems occur. In this formula the computer would have to process calculations on over one million rows ... (nearly all of them unnecessarily) ... and do that multiple times.
Try using whole columns in that formula and you can see what we mean. You will wait a long time if in fact the computer doesn't run out of resources first.
Strange : Old Formula im Copy pasting then formula is working but when im typing its not working Why So
You are probably mistyping something. Paste here what you have manually typed, otherwise we can't say.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks