Countifs multiplier

[neilskcorp]

Hello i have the following Excel workbook.

i have a data sheet "data" and a reporting sheet "tracking"

I have a countifs formula that counts if a range has a certain month and a certain component type.

Example i want to know how many Torque Converters we built in October. This works fine.
=COUNTIFS(DATA!$A:$A,"10",DATA!E:E,Tracking!$Q$15)


The problem i have is that there is another column. it is C:C which is Quantity.

I need to include the multiples in the count.

for example there is a list like below

month qty component
10 1 TC
10 1 TC
10 2 TC
11 1 TC
12 1 TC

i need it to count the TC's in the month 10. factoring in the multiplier.
ie my current countifs would give me 3, but i need it to realise that the 2 adds an additional one to it.

[Moo the Dog]

Where is 'TC' anywhere on this sheet?

[Moo the Dog]

Please Login or Register  to view this content.

That formula will add up the quantity of HYDRAULIC CYLINDERS for the month of October. So that formula would be placed on the TRACKING sheet in cell K21.

I used a range of 2000 rows just for an example. you can set it however you want otherwise.

- Vince

[neilskcorp]

Thank you for the help, how does this formula work, save me having to ask again.

cheers

[Moo the Dog]

Please Login or Register  to view this content.

The SUMPRODUCT function can be used to add the products of multiple lists/arrays. In this case, the formula is looking through two lists.
What it is basically doing is searching the 'Components' column for whatever is in cell Tracking!$Q$15 (Hydraulic Cylinders) *AND* the 'Date' column for Month=10 (October). If those conditions are both true, it returns the number in that same row from the 'Quantity' column.

It does that for every row in the list/array (so you have to make sure the ranges are all the same size), then adds them all together. So, for your example, the formula found 11 rows where the two conditions matched, and returned the sum of the quantities in that range. Since three of the rows had a quantity of 2 and 8 of the rows had a quantity of 1, the result is 14 (2+2+2+1+1+1+1+1+1+1+1).

By the way, the formula above can also be written this way:

Please Login or Register  to view this content.

There are a ton of websites with more information than I have time to provide here... just do a search for EXCEL SUMPRODUCT and you'll hit the jackpot.

- Vince