-4

Related to the attached spreadsheet, (I have already created a dashboard that contains the list of regions)

Count the number of unique Items for each region IF Item Category equals N/B/C/H/P

Sum of the prices of the unique items for each region IF Item Category equals N/B/C/H/P

I have tried a bunch of combos, including Sumproduct but its not working out.

enter image description here

Dominique
  • 16,450
  • 15
  • 56
  • 112
Mystic-1
  • 3
  • 1
  • 3
  • 1
    Can you include that bunch of combos you tried? – JvdV May 10 '19 at 07:07
  • Also, to count your unique values, I recently answered a fairly similar question [here](https://stackoverflow.com/questions/56059103/not-able-to-find-unique-values-in-excel/56059438#56059438). You can modify that so that `=SUM(--(FREQUENCY(IF(($B$2:$B$100="Batemans")*($E$2:$E$100=TRANSPOSE({"N";"B";"C";"H";"P"})),MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW(A2)+1)>0))` will give you the unique items count. – JvdV May 10 '19 at 08:32
  • Hi Dom! Thanks for the response. I tried using =COUNTIFS(A2:A30,A2:A30,B2:B30,G3&"",E2:E30,"N"). Its working somewhat but could you tell me how to incorporate a SUMPRODUCT function into this so that the result is a unique count? – Mystic-1 May 11 '19 at 13:51
  • I tried the formula you gave but for some reason it didnt work? It could be something I am doing wrong at my end but I'm afraid my Excel chops arent developed enough to figure out what went wrong :S – Mystic-1 May 11 '19 at 13:53
  • Dear Mystic-1 , please add the additional info into the question post.. – p._phidot_ May 14 '19 at 03:23

1 Answers1

0

Assuming all data is located in A2:F30.

Put in these labels :

H1  ->  region - item
I1  ->  count
J1  ->  prc1
K1  ->  prc2

then these formulas

H2  ->  =IF(COUNTIF($F$2:F2,F2)=1,F2,"")
I2  ->  =IF(H2="","",COUNTIFS(B:B,B2,E:E,E2))
J2  ->  =IF(H2="","",SUMIFS(C:C,B:B,B2,E:E,E2))
K2  ->  =IF(H2="","",SUMIFS(D:D,B:B,B2,E:E,E2))

and drag downwards. done.

You just need to filter to get a consolidated table.

Hope it helps.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17