Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
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!! |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |