Make a COUNTIF calculation take the data in another column into consideration

Hi,

I'm back again with another question. But this time I've made an example workbook.

Basically, I have a document like the attached one, where I'm keeping track of the license usage for our customers. In the E column (purple table) you have a dropdown list where you can choose the license. It will automatically show the unit price in the F cell next to it.

At the bottom, in the green table, there's a list of all the available licenses. In the column called "Amount" I have a COUNTIF formula, which checks how many times a license name occurrs in the purple table. There's one problem though; the COUNTIF formula doesn't take into consideration the amount of licenses specified in the D column.

E.g. on row 2 you can see the license code for Windows Server Web Edition (cell E2). In cell D2 you see that we need 2 of those licenses. If I do a COUNTIF on only row 2 it'll tell me that the Web Edition license occurs once - but I need it to tell me that that row uses 2 licenses (D2 x E2). Does anyone know how to do that?

This is a very small table, on most of my tables there are 50-200 rows. It's just too time-consuming to count them manually.

The document doesn't contain anything confidential. All product names are official product names released by MS. If anyone wants to use the formulas - enjoy ^^

I'm thankful for any help or if someone can point me in the right direction.

Best regards,
Corm

Hi,

Instead of using COUNTIF(), you can use SUMIF(). In B26:

=SUMIF($E$2:$E$22,$A26,$D$2:$D$22)