How can I incorporate a quantity field into COUNTIF function?

1
votes

I am using a COUNTIFS function to compare forecast data from a customer (Sorted AC Forecast) with our current orders (Stock & Forecast).

=COUNTIFS('Sorted AC Forecast'!$A:$A;'Stock & Forecast'!$B5;'Sorted AC Forecast'!$B:$B;'Stock & Forecast'!H$3;'Sorted AC Forecast'!$C:$C;"FUTURE NEEDS")

I basically check for matches from the customer's spreadsheet for the following: 1. Model Number, 2. Delivery Week, 3. Order Type. So, if the model number in B5 in my spreadsheet is found in the customers' data, if the delivery week specified in H3 is found in the customers' data, and if order type matches, the match is counted.

The issue I am facing now is that our customer changed the format of their datasheet; it now includes a "Quantity" category. That means that I need to incorporate the quantity, for instance if COUNTIF counts 2 matches and the quantity field reads 1 and 4 respectively, I need it to return 5.

I have attached some links to pictures of the two sheets to try and make the issue clearer.

http://i.stack.imgur.com/NqQeN.jpg

http://i.stack.imgur.com/z3ZGz.jpg

Any suggestions? I've tried using a macro to print out 3 duplicate rows if the quantity is specified as 3, only its slow and doesn't work very well.

1
excelcountif
Is there a chance you can inline those links here? That would help a bit. Also, welcome to Stack Overflow! - Isiah Meadows
Hello Isiah! Thanks for the welcome! Im not sure what you mean by "inline"? - the19
You can do it like this. - Isiah Meadows
Aha! I see! Happily my problem has been solved now, thanks anyway! - the19

1 Answers

0
votes

Use SUMIFS to match the criteria and then total the "Quantity" column if the criteria match. For example if the "Quantity" column was column D:D:

SUMIFS('Sorted AC Forecast'!$D:$D,'Sorted AC Forecast'!$A:$A;'Stock & Forecast'!$B5;'Sorted AC Forecast'!$B:$B;'Stock & Forecast'!H$3;'Sorted AC Forecast'!$C:$C;"FUTURE NEEDS")