using countif to autofill table info

[b16dlg]

Good morning

firstly apologies for the title as struggling to explain what I am after so have attached a sample sheet which explains.

basically the1st table utilises vlookup on the second table and auto fills another table which I have already set up which has background calculations based on results from the first table.

eg of what i cannot do

I have a quantity of 7 (Column B) and it is quality type 21 (Column C) which gives a code of A (Column D) when i do a countif based on code A i get the answer as 1 which is correct because it occurs once however i need the total to show it occurs once yes but we will be making 7 of them i.e. quantity 7

I wish to only have to put info into columns B & C as we have literally thousands of qualities but these condense into only 9 different codes hence the reason for the table.

I would have used a pivot table but as the info feeds into another table that has to have a 0 or quantity against each of the 9 codes a pivot table would not work as each time the table was created the cell reference would change.

sorry for being confusing the sample table does make things easier but i'm sure there is a simple solution and that i should be using a different function to countif.

all help gratefully received

Damion

[Fotis1991]

Hi

I would like to helps you, but i am not able to understand your target...

As first table(B4:D27), is empty...then countif, gives 0. Give me an example of a correct result..

[b16dlg]

hi sorry created the mock up but didnt put any info in the table

i have ammended the attachment now

[Fotis1991]

So in D28 and copy down, use this(As you are in Excel2003)

=SUMPRODUCT(($D$4:$D$27=C28)*($B$4:$B$27))

Is this, works for you?

[b16dlg]

That is exactly what I am after

Thank you very much for your help

[Fotis1991]

You are welcome

Thank you for the feed back and also for reb*