hi all... can I ask for some help with this please?
i've got 2 worksheets a I trying to pull data from them and summarise it into a 3rd worksheet.
my worksheets contain information relating to orders placed before and after specific dates. I need to have my 3rd worksheet check through a list of items (product id in Col D) and tell me how many matches are found in one of the other sheets before a date found in Col F of my 3rd sheet.
my formula so far is as below, but its returning the wrong figure. I physically count 6 matches for all dates for this item, however only 3 of them are placed BEFORE the date in Col F.
=SUM((COUNTIF('RAW DATA - all req item dmds'!C2:C500,D2))-(COUNTIF('RAW DATA - all req item dmds'!G2:G500,"< f2")))
Also, for clarity the formula references these columns:
Effectively I'm trying to do the following (excuse layman's terms):
(Total of all orders placed of [Item 1]) minus (all orders of [item 1] after [this date])
[Item 1] and [this date] are specified on the same sheet as my formula, and all info relating to the orders (item code, date of order etc) are on another sheet, also there will be duplicated lines if any one item was ordered on different days
i've got 2 worksheets a I trying to pull data from them and summarise it into a 3rd worksheet.
my worksheets contain information relating to orders placed before and after specific dates. I need to have my 3rd worksheet check through a list of items (product id in Col D) and tell me how many matches are found in one of the other sheets before a date found in Col F of my 3rd sheet.
my formula so far is as below, but its returning the wrong figure. I physically count 6 matches for all dates for this item, however only 3 of them are placed BEFORE the date in Col F.
=SUM((COUNTIF('RAW DATA - all req item dmds'!C2:C500,D2))-(COUNTIF('RAW DATA - all req item dmds'!G2:G500,"< f2")))
Also, for clarity the formula references these columns:
- D2 = item code
- "RAW DATA - all req item dmds'!C2:C500 " = particular item code ordered
- "RAW DATA - all req item dmds'!G2:G500" = date that order was placed
Effectively I'm trying to do the following (excuse layman's terms):
(Total of all orders placed of [Item 1]) minus (all orders of [item 1] after [this date])
[Item 1] and [this date] are specified on the same sheet as my formula, and all info relating to the orders (item code, date of order etc) are on another sheet, also there will be duplicated lines if any one item was ordered on different days
Last edited by a moderator: