Countif function

[navigator25]

Im trying to use the countif function to calculate totals. best way to try and explain is to go into this so here it is.

A16:A55 is the qty's
B16:B55 is the sizes

Im trying to first get all the sizes listed in B16:B55, im not sure the function to get all the distinct values. Ive been just free handing it at the moment, which is dangerous since theres a chance i miss a size.

Then I need to find out how many times they come up, which is why im trying to use the countif function.

However what i dont know how to do is get it to consider the quantity in the A column.

for example in A16 i have 6 and B16 I have 20x25x1
A17 i have 8 and B17 I have 20x20x1
A18 i have 4 and B18 I have 16x20x1
A19 i have 2 and B19 I have 20x25x1

Im trying to get a result of QTY Size
8 20x25x1
8 20x20x1
4 16x20x1

Hope this is enough info and of course there is going to be a lot more variable's and different sizes possible.

Thanks

[JBeaucaire]

The function you want is SUMIF().

=SUMIF(MatchRange, MatchValue, SumRange)


=SUMIF(B:B, "20x25x1", A:A)
=SUMIF(B:B, D3, A:A)

[navigator25]

The function you want is SUMIF().

=SUMIF(MatchRange, MatchValue, SumRange)


=SUMIF(B:B, "20x25x1", A:A)
=SUMIF(B:B, D3, A:A)

Thanks that will work great for adding all the qty's up. Is there a quick way to find out all the sizes without doing that by hand, so that i don't have a chance of missing one as i go through all of them?

[navigator25]

I ended up doing to the Advanced Filter Function to accomplish this, however is there any way I can skip rows that i dont want included into the function?

Ive got it setup like this at the moment.

Copy to another Location Selected
List Range = $B$16:$B$110
Criteria Range = $A$16:$A$110
Copy To Range = $AF$16

with the check box for unique records only.

However when there is more than 1 sheet (anything after Row 55 on my sheet) There is a heading i would like it to skip over. In other words I would like to to add up my list range as follows B16:B55 & B71:B110 not B56:B70. Every time i try something in there it doesn't allow me to do that. As well as do the same thing with the Criteria Ranges.

Any Thoughts?

[JBeaucaire]

ADV filter will skip one title range. I'm sure you can run your ADV filter, sort the resulting list and delete the duplicate headers without much problem.