Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with formula
I use this current formula to keep track of cars sold in a month. I want to
keep track of the different models sold at our store in a month and have paid cash. I keep a log in Worksheet1 and I want to have the results show in worksheet2. Is there a way of streamlining the date portion to equal the heading in Worksheet2? Worksheet1 SUMPRODUCT((WORKSHEET1!$A$3:$A$1737="JANUARY")*(WO RKSHEET1!$C$3:$C$1737="MODEL-A")*(WORKSHEET1!$J$3:$J$1737="CASH")*WORKSHEET!$H$ 3:$H$1737) Worksheet2 JAN FEB MAR APR MAY JUN JUL AUG SEPT OCT NOV DEC Model-A Model-B Model-C Any help is greatly appreciated, Thanks, |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with formula
Assuming in real dates in Sheet1's col A,
with text "months" eg: Jan, Feb, etc in Sheet2's B1 across, models listed in Sheet2's A2 down In Sheet2, Place in B2: =SUMPRODUCT((TEXT(Sheet1!$A$3:$A$1737,"mmm")=B$1)* (Sheet1!$C$3:$C$1737=$A2)*(Sheet1!$J$3:$J$1737="CA SH")*Sheet1!$H$3:$H$1737) Copy B2 across/fill down to populate (Adapt to suit your actual sheetnames. I'd use very short sheetnames, which keeps formulas short n sweet) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "Joseph" wrote: I use this current formula to keep track of cars sold in a month. I want to keep track of the different models sold at our store in a month and have paid cash. I keep a log in Worksheet1 and I want to have the results show in worksheet2. Is there a way of streamlining the date portion to equal the heading in Worksheet2? Worksheet1 SUMPRODUCT((WORKSHEET1!$A$3:$A$1737="JANUARY")*(WO RKSHEET1!$C$3:$C$1737="MODEL-A")*(WORKSHEET1!$J$3:$J$1737="CASH")*WORKSHEET!$H$ 3:$H$1737) Worksheet2 JAN FEB MAR APR MAY JUN JUL AUG SEPT OCT NOV DEC Model-A Model-B Model-C Any help is greatly appreciated, Thanks, |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with formula
I tried it and for some reason cannot make it work.....
These are the headings that I currently use in Worksheet1: Columns-- A- Month (JAN,FEB,MAR,etc) B- Date (mm/dd/yyyy) C- Model D- Cust Name E- Type F- Amount paid G- Product A purchased (aftermarket products) H- Product B purchased (Extended warranty) I- Product C purchased (protection package) J- Category I appreciate any ideas you can provide me with,,,, Thank you, "Max" wrote: Assuming in real dates in Sheet1's col A, with text "months" eg: Jan, Feb, etc in Sheet2's B1 across, models listed in Sheet2's A2 down In Sheet2, Place in B2: =SUMPRODUCT((TEXT(Sheet1!$A$3:$A$1737,"mmm")=B$1)* (Sheet1!$C$3:$C$1737=$A2)*(Sheet1!$J$3:$J$1737="CA SH")*Sheet1!$H$3:$H$1737) Copy B2 across/fill down to populate (Adapt to suit your actual sheetnames. I'd use very short sheetnames, which keeps formulas short n sweet) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "Joseph" wrote: I use this current formula to keep track of cars sold in a month. I want to keep track of the different models sold at our store in a month and have paid cash. I keep a log in Worksheet1 and I want to have the results show in worksheet2. Is there a way of streamlining the date portion to equal the heading in Worksheet2? Worksheet1 SUMPRODUCT((WORKSHEET1!$A$3:$A$1737="JANUARY")*(WO RKSHEET1!$C$3:$C$1737="MODEL-A")*(WORKSHEET1!$J$3:$J$1737="CASH")*WORKSHEET!$H$ 3:$H$1737) Worksheet2 JAN FEB MAR APR MAY JUN JUL AUG SEPT OCT NOV DEC Model-A Model-B Model-C Any help is greatly appreciated, Thanks, |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with formula
I tried it and for some reason cannot make it work.....
Let's not waste time. The expression should have worked (I tested it before posting). So, probably data inconsistency somewhere. Upload your sample file using a free filehost, then post a link to it here. Use this free, easy to use filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here in reply. Desensitize your sample as appropriate. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,000 Files:359 Subscribers:56 xdemechanik --- "Joseph" wrote in message ... I tried it and for some reason cannot make it work..... These are the headings that I currently use in Worksheet1: Columns-- A- Month (JAN,FEB,MAR,etc) B- Date (mm/dd/yyyy) C- Model D- Cust Name E- Type F- Amount paid G- Product A purchased (aftermarket products) H- Product B purchased (Extended warranty) I- Product C purchased (protection package) J- Category I appreciate any ideas you can provide me with,,,, Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|