Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: SearchAndGet a value Help
Hello
I am rather newbie in excel and need a help with a certain problem I have. On the following url (http://www.fuchka.info/tmp/excel.jpg) You can see a screencapture of excel worksheet and sample data. I need a formula for right table which will scan left table and: 1. compare the "date" (example: columns "H" and "I" should get values only for month June) and then 2. compare the "code" (example: rows in column "G" should return values (from income/outcome cells) corresponding to code number in a row in column E. So .. how do I get "I5" cell ... I check column A for a date in range from June 1st to June 31st, after that I take ALL VALUES from column C for which in column E is code 100. So in my case I have only one value ($100,00). If i would have more then one value, the function should AutoSum them. And now in cell "I5" I get "$100,00". so one more time ... function should check for a specific date range (month) and then for specific code (100 or 101 or 102 or etc) and return SUM of all values in corespoding columns "B" or "C" (depends on income or outcome values). (outcome: if date=june and code=100 return all values from C and autosum them) So can anybody help me with this problem ...? And sorry for my bad english .. :D |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: SearchAndGet a value Help
for H5:
=SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($B$5:$B$1000)) for I5: =SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($C$5:$C$1000)) Copy both down as far as required, changing 1000 as required For columns J & K, copy the above changing 6 to 7 For columns L & M, copy the above changing 6 to 8 etc HTH " wrote: Hello I am rather newbie in excel and need a help with a certain problem I have. On the following url (http://www.fuchka.info/tmp/excel.jpg) You can see a screencapture of excel worksheet and sample data. I need a formula for right table which will scan left table and: 1. compare the "date" (example: columns "H" and "I" should get values only for month June) and then 2. compare the "code" (example: rows in column "G" should return values (from income/outcome cells) corresponding to code number in a row in column E. So .. how do I get "I5" cell ... I check column A for a date in range from June 1st to June 31st, after that I take ALL VALUES from column C for which in column E is code 100. So in my case I have only one value ($100,00). If i would have more then one value, the function should AutoSum them. And now in cell "I5" I get "$100,00". so one more time ... function should check for a specific date range (month) and then for specific code (100 or 101 or 102 or etc) and return SUM of all values in corespoding columns "B" or "C" (depends on income or outcome values). (outcome: if date=june and code=100 return all values from C and autosum them) So can anybody help me with this problem ...? And sorry for my bad english .. :D |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: SearchAndGet a value Help
Assuming real dates in A5:A10, and 1st-of-month dates placed in H3, J3, K3.
H3:I3, J3:K3, L3:M3 are assumed formatted to "Center across selection" via Format Cells Alignment tab Text alignment Horiz droplist (last selection) A sample construct is available at: http://www.savefile.com/files/7207078 Summarizing by month n product code via sumproduct.xls In H5, copied to I5, filled down to I8: =SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($H$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10) In J5, copied to K5, filled down to K8: =SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($J$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10) In L5, copied to M5, filled down to M8: =SUMPRODUCT((TEXT($A$5:$A$10,"mmm-yy")=TEXT($L$3,"mmm-yy"))*($E$5:$E$10=$G5),B$5:B$10) The 3 formulas above are similar, except pointing to H3, J3, L3 respectively For a neater look, extraneous zeros are suppressed from displaying in the sheet via clicking Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hello I am rather newbie in excel and need a help with a certain problem I have. On the following url (http://www.fuchka.info/tmp/excel.jpg) You can see a screencapture of excel worksheet and sample data. I need a formula for right table which will scan left table and: 1. compare the "date" (example: columns "H" and "I" should get values only for month June) and then 2. compare the "code" (example: rows in column "G" should return values (from income/outcome cells) corresponding to code number in a row in column E. So .. how do I get "I5" cell ... I check column A for a date in range from June 1st to June 31st, after that I take ALL VALUES from column C for which in column E is code 100. So in my case I have only one value ($100,00). If i would have more then one value, the function should AutoSum them. And now in cell "I5" I get "$100,00". so one more time ... function should check for a specific date range (month) and then for specific code (100 or 101 or 102 or etc) and return SUM of all values in corespoding columns "B" or "C" (depends on income or outcome values). (outcome: if date=june and code=100 return all values from C and autosum them) So can anybody help me with this problem ...? And sorry for my bad english .. :D |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: SearchAndGet a value Help
THANK YOU! :d
Toppers wrote: for H5: =SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($B$5:$B$1000)) for I5: =SUMPRODUCT(--(MONTH($A$5:$A$1000)=6),--($E$5:$E$1000=$G5),($C$5:$C$1000)) Copy both down as far as required, changing 1000 as required For columns J & K, copy the above changing 6 to 7 For columns L & M, copy the above changing 6 to 8 etc HTH " wrote: Hello I am rather newbie in excel and need a help with a certain problem I have. On the following url (http://www.fuchka.info/tmp/excel.jpg) You can see a screencapture of excel worksheet and sample data. I need a formula for right table which will scan left table and: 1. compare the "date" (example: columns "H" and "I" should get values only for month June) and then 2. compare the "code" (example: rows in column "G" should return values (from income/outcome cells) corresponding to code number in a row in column E. So .. how do I get "I5" cell ... I check column A for a date in range from June 1st to June 31st, after that I take ALL VALUES from column C for which in column E is code 100. So in my case I have only one value ($100,00). If i would have more then one value, the function should AutoSum them. And now in cell "I5" I get "$100,00". so one more time ... function should check for a specific date range (month) and then for specific code (100 or 101 or 102 or etc) and return SUM of all values in corespoding columns "B" or "C" (depends on income or outcome values). (outcome: if date=june and code=100 return all values from C and autosum them) So can anybody help me with this problem ...? And sorry for my bad english .. :D |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: SearchAndGet a value Help
just one more thing please ... it just came up ...
is it possible in the same way to set code (example: 100) and date (example: month June) and get a list of entire rows with all values? example from my previous table: I enter the code value: "100" and month: "6" and get all the rows with code 100 and month June date / income / outcome / description / code 6 / $150 / / Bla Bla / 100 / 6 / / $100 / Bla Bla / 100 thnx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: SearchAndGet a value Help | Excel Discussion (Misc queries) |