If(countif),0

[Closed]
Report
-
 Al -
Hello,

looked up many, many site for help but can not quite get the solution. i have a database that displays particular models of cell phones and their respective distribution centers. there are only 2 counties (CA or US) which need to be looked up, then model types counted in the country. e.g.

MODEL (A) SERIAL (B) STREET AD (C) CITY (D) STATE( E) COUNTRY (F)
NOKIA 450 xxxx xxxx xxxx xxxx UNITED STATES
NOKIA 320 xxxx xxxx xxxx xxxx UNITED STATES
SAMSUNG 4 xxxx xxxx xxxx xxxx UNITED STATES
NOKIA 450 xxxx xxxx xxxx xxxx CANADA
NOKIA 450 xxxx xxxx xxxx xxxx UNITED STATES
LG 1200 xxxx xxxx xxxx xxxx CANADA

the summary will be listed on a separate sheet to appear like this

UNITED STATES

MODEL (A) QUANTITY (B) %'AGE TOTAL (C)
NOKIA 450 ---- 2 ----- 33%
NOKIA 320 ---- 1 ----- 18%
SAMSUNG 4 ---- 1 ----- 18%
LG 1200 ---- 0 ----- 0%

used this formula but it ALWAYS counts the ENTIRE TOTAL.
=SUM(IF('DB'!Q:Q="CANADA",COUNTIF("DB'!C:C,sheet1=A3),0)) and if i SUM it, then a TRUE/FALSE appears.
Any input of course would be appreciated. Thanks in advance.

2 replies

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Hello,
a Pivot Table is meant to do easily what you try to do and is surely the best solution.
but you can also use the function SUMPRODUCT with a * between the different conditions
=SUMPRODUCT(('DB'!Q:Q="CANADA")*('DB'!C:C="NOKIA 450"))
Ivan
Hello,

I tried that suggestion and now see a #NUM error. I understand about the pivot table which I did try but it will not list the models on the summary page i have that will not be instituted until year-end. It only shows current models.