SUM of COUNTIF with multiple criteria

si3po

Board Regular
Joined
Jan 7, 2019
Messages
98
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

  • 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:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top