countif

M

Myriam

Once again, I need your help.
I am trying to count items by quantity and by size. I can do it by one not
both.
with the formula
=IF(COUNTIF($O$5:O5,O5)=1,SUMIF($O5:$O$253,O5,$P$5:$P5:$253).0)

O P Q
Item Qty Size

A 2 1"
B 3 2"
C 2 2"
B 1 2"
etc.

I don't know how to add an AND statement to include column Q (size). i.e.,
look for the item with the same name AND same size THEN count them.

I really appreciate any help you could give me.
Thanks!!
 
M

Max

Perhaps quite ideal to use a pivot table (PT) for this
(only a few steps to get there)

With the sample table below in O1:Q5

Item Qty Size
A 2 1"
B 3 2"
C 2 2"
B 1 2"

Select any cell within the table

Click Data > Pivot table Report
Click Next > Next

In Step 3 of the wizard:
Drag and drop Item within the ROW area
Drag and drop Size within the COLUMN area
Drag and drop Qty within the DATA area
(It'll appear as Sum of Qty)

Click Finish

The PT will be created in a new sheet to the left,
with the desired results:

Sum of Qty Size
Item 1" 2" Grand Total
A 2 2
B 4 4
C 2 2
Grand Total 2 6 8
 
G

gazornenplat

Suggest a helper column of =O1 & Q1 and use that

Or have a look at CSE formulae

Gaz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top