Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct with Date column
I have multiple columns where one contains a date formated as 6/6/2005 for
example. I have other columns with the rep id and product numbers. I want to show how many of a product did a rep sell on a particular date. The problem I'm having is getting the formula to work with the date. The data looks something like this. Any sugegstions? A B C JS 253 6/6/2005 BO 134 6/3/2005 BO 253 6/6/2005 -- JerryS |
#2
|
|||
|
|||
Assuming the sample data is in A1:C3,
you could use in say, D1: =SUMPRODUCT((A1:A3="BO")*(C1:C3=DATE(2005,6,3)),B1 :B3) which returns the number 134 (for the rep "BO" on 3 Jun 2005) Note that you can't use entire col refs in SUMPRODUCT and that the ranges: A1:A3, C1;C3 etc should be identical in structure -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "JerryS" wrote in message ... I have multiple columns where one contains a date formated as 6/6/2005 for example. I have other columns with the rep id and product numbers. I want to show how many of a product did a rep sell on a particular date. The problem I'm having is getting the formula to work with the date. The data looks something like this. Any sugegstions? A B C JS 253 6/6/2005 BO 134 6/3/2005 BO 253 6/6/2005 -- JerryS |
#3
|
|||
|
|||
just put a date formatted the same into a cell somewhere (a1)
=sumproduct((c2:c2000=a1)*1) to count the sales on that date -- Don Guillett SalesAid Software "JerryS" wrote in message ... I have multiple columns where one contains a date formated as 6/6/2005 for example. I have other columns with the rep id and product numbers. I want to show how many of a product did a rep sell on a particular date. The problem I'm having is getting the formula to work with the date. The data looks something like this. Any sugegstions? A B C JS 253 6/6/2005 BO 134 6/3/2005 BO 253 6/6/2005 -- JerryS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Excel: Add time to a date and time column? | Excel Worksheet Functions | |||
sumproduct column index | Excel Worksheet Functions | |||
Date in descending order to come up automatically in a column? | Excel Worksheet Functions | |||
DATE FORMATTED COLUMN ERROR | Excel Worksheet Functions |