Sum column and count distinct values in another based on multiple criteria

Hi All, I need help with problem I've been wrestling with for a couple of week now (and until now have been too proud to ask for help on). I have a table with multiple columns (20 in total). However I would like a someone to help with with a formula that looks at only needs to consider three of them.

The first two columns are references and the third will contain a negetive number. I would like someone to help me with a formula that does the following
Criteria 1: Looks at the first column and finds all the references that are exactly the same (ie BATCH/0407), then
Criteria 2: Looks at the second column that has all references that are the same (for criteria 1) [ie 830 REVISE PATH (WITH CABL]
Criteria 3: Add together all the the numbers in the third column that match criteria 1 & 2 (ie add -20 to -42)

Sample data is below (hopefully the columns align). If not I've uploaded sample data in spread sheet named Example data.xlsx

However, I also would like the results to show the Batch and Type too (as these result will be on another sheet with the workbook and -44 on it's own won't mean much. Using the ones with the green background (for example) the result would be -44. I have also included a table of expected results.

So I guess the actual result for the example would be │Batch/0621 │ 810 New Path (Cable) │ -44 │and I would like this formula to do this for the whole spreadsheet (which contains several thousand entries)

I would be grateful for any assistance anyone can offer.


Batch │ Type │ Ontime / Early
BATCH/0072 850 REVISE PATH (WITHOUT C -20
BATCH/0407 830 REVISE PATH (WITH CABL -20
BATCH/0407 830 REVISE PATH (WITH CABL -42
BATCH/0439 850 REVISE PATH (WITHOUT C -11
BATCH/0440 850 REVISE PATH (WITHOUT C -11
BATCH/0477 850 REVISE PATH (WITHOUT C -20
BATCH/0619 902 NEW CUSTOMR (NO CABLE) -20
BATCH/0619 902 NEW CUSTOMR (NO CABLE) -5
BATCH/0621 810 NEW PATH (CABLE) -20
BATCH/0621 810 NEW PATH (CABLE) -12
BATCH/0621 810 NEW PATH (CABLE) -1
BATCH/0621 810 NEW PATH (CABLE) -8
BATCH/0621 810 NEW PATH (CABLE) -3
BATCH/0661 820 NEW PATH (NO CABLE) -20
BATCH/0661 820 NEW PATH (NO CABLE) -1
BATCH/0661 820 NEW PATH (NO CABLE) -10

Thank you in advance for all those who attempt to help.

Hi SK,

Thank you for your efforts and speedy response. Sadly the results you included in column J do not match those in column I in my expected output example.

Pl see file, ARRAY formulas are used.(extra spaces are removed)

ARRAY Formula for 1st and 2nd column

Please Login or Register  to view this content.

Formula for 3rd column

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

Hi memahobt,

I've included the same sample data, but with the results expected in columns F to H

Thanks

Hi mcmahobt, that works perfectly for the Ontime / Early column, however, I also need the formula that would give the Batch Qty for the given type (sorry, it's me, I didn't explain it very well).

Spot on again. You're awsome!!

You have really helped me. I spent all weekend trying to work these problems out (without success).

Thank you so much