CountIf Error

[Tiffany08]

Hi All,

Can someone please help me. I'm trying to do a CountIf for the number of stores based on the Product Description:

=IF(C3=0,"",COUNTIF(DATA!$D$2:$D$57356,A3))

# Stores Product Description Sum of CY
101 "Promo 14"" Tool Box Kit" 7094


However my formula is flawed as it is counting all the repeat products when there are only 54 stores. Please see the DATA below:

MATERIAL ID STORE STATE PRODUCT DESCRIPTION
N4009155-UNIT DC20 Darwin DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT DC20 Darwin DTC "Promo 14"" Tool Box Kit"
19385845-UNIT NC02 Lidcombe Mega DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC02 Lidcombe Mega DTC "Promo 14"" Tool Box Kit"
N4010030-UNIT NC02 Lidcombe Mega DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC02 Lidcombe Mega DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC07 Taren Point DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC08 Blakehurst DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT NC08 Blakehurst DTC "Promo 14"" Tool Box Kit"
N4010040-UNIT NC08 Blakehurst DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC08 Blakehurst DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT NC09 Lambton DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC09 Lambton DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC09 Lambton DTC "Promo 14"" Tool Box Kit"
N4010040-UNIT NC09 Lambton DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC09 Lambton DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC10 Gosford West DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC12 Chippendale DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT NC12 Chippendale DTC "Promo 14"" Tool Box Kit"
N4010050-UNIT NC12 Chippendale DTC "Promo 14"" Tool Box Kit"
N5210038-UNIT NC12 Chippendale DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC13 Wollongong DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC13 Wollongong DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC14 Brookvale DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT NC14 Brookvale DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC16 Parramatta North DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC17 Castle Hill DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC19 Liverpool DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC19 Liverpool DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT NC19 Liverpool DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT NC24 Fyshwick DTC "Promo 14"" Tool Box Kit"
N4009206-UNIT NC24 Fyshwick DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT NC24 Fyshwick DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT NC24 Fyshwick DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT QC01 Newstead DTC "Promo 14"" Tool Box Kit"
N4009208-UNIT QC01 Newstead DTC "Promo 14"" Tool Box Kit"
N4009213-UNIT QC01 Newstead DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT QC03 Browns Plains DTC "Promo 14"" Tool Box Kit"
N4009228-UNIT QC03 Browns Plains DTC "Promo 14"" Tool Box Kit"
N4010040-UNIT QC03 Browns Plains DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT QC09 Toowoomba DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT QC11 Burleigh Heads DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT QC11 Burleigh Heads DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT QC22 Lismore DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT QC33 Maroochydore DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT QC33 Maroochydore DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT QC35 Cairns DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT QC66 Underwood DTC "Promo 14"" Tool Box Kit"
N4009213-UNIT QC66 Underwood DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT QC66 Underwood DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT QC69 Townsville DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT SC02 Glynde DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT SC02 Glynde DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT SC02 Glynde DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT SC03 Clovelly Park DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT SC07 Mile End DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT SC07 Mile End DTC "Promo 14"" Tool Box Kit"
N4010050-UNIT SC07 Mile End DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT SC07 Mile End DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4009206-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4009208-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4009209-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4010010-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N4010011-UNIT TC42 Launceston DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4009078-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4010010-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4010011-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4010030-UNIT TC43 Hobart DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC03 Sunshine DTC "Promo 14"" Tool Box Kit"
N4010030-UNIT VC03 Sunshine DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC05 Hoppers Crossing DTC "Promo 14"" Tool Box Kit"
N4010030-UNIT VC05 Hoppers Crossing DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC13 Preston Mega DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT VC13 Preston Mega DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT VC20 Ballarat DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC22 Shepparton DTC "Promo 14"" Tool Box Kit"
N4009207-UNIT VC22 Shepparton DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC26 Geelong West DTC "Promo 14"" Tool Box Kit"
N5210038-UNIT VC26 Geelong West DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT VC27 Springvale DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT VC27 Springvale DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT WC03 Cannington DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT WC03 Cannington DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT WC03 Cannington DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT WC04 Osborne Park DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT WC04 Osborne Park DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT WC04 Osborne Park DTC "Promo 14"" Tool Box Kit"
N1202000-UNIT WC12 Malaga DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT WC12 Malaga DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT WC12 Malaga DTC "Promo 14"" Tool Box Kit"
N4010011-UNIT WC12 Malaga DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT WC12 Malaga DTC "Promo 14"" Tool Box Kit"
N4009155-UNIT WC15 Rockingham DTC "Promo 14"" Tool Box Kit"
N4010000-UNIT WC15 Rockingham DTC "Promo 14"" Tool Box Kit"
N5600001-UNIT WC15 Rockingham DTC "Promo 14"" Tool Box Kit"

Thank you all for your help

Regards,
Tiff

[ConneXionLost]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[teylyn]

Hello,

this looks like you need a pivot table to sum/count/slice/dice your data. Get started here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Very powerful and easy to set up.

cheers,