countif
 

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!!

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Myriam" wrote in message
...
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!!

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

Or have a look at CSE formulae

Gaz


--
gazornenplat


------------------------------------------------------------------------
gazornenplat's Profile: http://www.excelforum.com/member.php...o&userid=24494
View this thread: http://www.excelforum.com/showthread...hreadid=381080