if(countif),0Closed

Question

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.

Ivan-hoe · Answer

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

Al · Answer

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.

If(countif),0

2 responses

Similar discussions

Subscribe To Our Newsletter!