Please go through the attachment for requirement and help me with the right formula.
regards,
vimala
Please go through the attachment for requirement and help me with the right formula.
regards,
vimala
Sorry for off-topic interjection:
Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
first sum the values in the column, after that
with HLookup
d12 =HLookup($C13,$A$2:$C$6,5,0)
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Or
try
D12=SUMPRODUCT(OFFSET($A$2,1,MATCH($C12,$B$2:$C$2,0),COUNTA($A$3:$A$22))) and drag down!!
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Another way array entered in D12 and filled down.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.
Dave
I just gave example as three items in row 1, 2, 3 i have total rows upto 2000 where these basic and arrears basic will appear in different rows. here in the formulas it is just adding based on the header.. but it should identify out of 2000 rows where this basic is existing and for the header 3100 it should add... hope this is clear...
It is Always good to add a example that match your real question.
Please add a new file and also add the expected result in your file.
Please find the new attachment with details...
with vlookup and sumif.
You have taken the required data to different region and gave "Yes" which is the unique source for the formula where i cant take the data to different place and give manual information and the formula is not only for basic it needs to the other rows also like Car + Car allowance, Loan + Loan Arrears etc...
Then fill in that criteria in the column L.
I would solve this with a pivot table, but for that i should restructure the data.
yaah.... it sounds good.. let me try and get my output..
If I have interpreted correctly another way.
Formula in D18 filled down and across.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 08-10-2016 at 02:02 PM.
I have changed the headers and tried with the provided formula but it is giving me result as 0. can you please help me on this.
attaching the template for your reference
This formula entered in D18:
=SUMIF($A$3:$A$14,"*"&$C18&"*",B$3:B$14)
Copy across to E18 then down to D19:E19.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
No i cant give the formula to the exact column where office 1 and office 2 existing cause i have two hundred columns in my raw data and its not easy to identify where this office 1 is existing. but the first portion of your formula for the rows is good cause it will find the c18 and total the cells where as column i cant give the formula manually and more over every month my data get changes for example this month office 1 is in b column next month it may go to the J column.
hope i didnt confuse you.
i have gave the example template with some different data columns... please refer and help me out.
Try this version...
=SUMIF($A$3:$A$14,"*"&$C18&"*",INDEX($B$3:$C$14,0,MATCH(D$17,$B$2:$C$2,0)))
I am getting the result as #NA
Try
d18=SUMIFS(INDEX($B$3:$F$14,,MATCH($C18,$B$2:$F$2,0)),$A$3:$A$14,"*"&D$17&"*") and drag towards the cells
the formula is working fine in the same sheet but if i am having the raw data in another sheet it is not giving me the right output.
Then you have to add the sheetreference in your formula.Please Login or Register to view this content.
Sorry if it's a wrong place to ask, I just see no reason to add new thread because I guess my excel homework question is really easy, but I can't cope with it. I've exported some data to Excel and there are too many empty rows mixed with data. Please tell me how can I delete all the empty rows. If it's possible.
@dieter
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.
Sorry. I'm newbie. I've noticed this question was answered today and was hoping for some Excel guru it will take few seconds to answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks