Need help with formula

J

Joseph

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")*(WORKSHEET1!$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,
 
M

Max

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="CASH")*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
 
J

Joseph

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,
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top