0

I have 3 columns

ORDER, ID and E Yes/NO

In Column C order In Column D ID In Column E Yes/No

For example for ID = 144. I need count to how many order it was given to 144. so looking at sample table below 144 was given 1 order which was 821 and column is Yes for 144 and when Order = 821.

Another example ID=162. was given 2 order , 861 and 992. so his total order count is 2 and # of Yes count is 1 because row 13 is No.

I am really stuck on this complex logic. Any feedback would be appreciated.

enter image description here

Community
  • 1
  • 1
Mowgli
  • 3,422
  • 21
  • 64
  • 88
  • 3
    Did you try to use `PivotTable`? I think it is good and quick solution for what you need... – Kazimierz Jawor Apr 16 '13 at 15:43
  • 1
    See this link http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values – Siddharth Rout Apr 16 '13 at 15:52
  • @KazJaw This is not one time report this is weekly report and data gets updated every week so I can't recreate pivot table everything, and I am not good at PiovtTable. – Mowgli Apr 16 '13 at 15:58
  • @SiddharthRout Thanks I'll try to play wit that post and see what I can gain from it thanks – Mowgli Apr 16 '13 at 15:59
  • 1
    @Mowgli, to be hones, PivotTable is worth to spend a while on learning how it works. Especially, if it is weekly report it will be easy and quick. How many rows do you have in data table?? is that number constant each week?? – Kazimierz Jawor Apr 16 '13 at 16:01
  • Total month could go aout 500-1000. weekly averages about it is random sometimes 200 or 300 more less. – Mowgli Apr 16 '13 at 16:02
  • @SiddharthRout How can I check for 3rd condition in my case which is to check Yes – Mowgli Apr 16 '13 at 16:05
  • 1
    By Adding the 3rd column in the formula? `=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)` – Siddharth Rout Apr 16 '13 at 16:13
  • @SiddharthRout Thanks, it will take me few extr steps but I believe you got me a solution. – Mowgli Apr 16 '13 at 16:37

1 Answers1

1

Given the layout shown, try these in B17 and C17 respectively, then fill down:

=SUM(IF(FREQUENCY(IF(MMULT(-(D$2:D$13=A17),1),C$2:C$13),C$2:C$13),1))
=SUM(IF(FREQUENCY(IF(MMULT((D$2:D$13=A17)*(E$2:E$13="YES"),1),C$2:C$13),C$2:C$13),1))
lori_m
  • 5,487
  • 1
  • 18
  • 29
  • This is brilliant solution thanks alot. I never heard for `MMULT` – Mowgli Apr 17 '13 at 13:19
  • 1
    Glad this worked for you. `MMULT` is just there to avoid needing to use CTRL+SHIFT+ENTER, this can sometimes make calculation faster too. – lori_m Apr 17 '13 at 20:07
  • Matrix multiplication does it return like 1 or 0 or something? – Mowgli Apr 18 '13 at 14:07
  • Why is that if I enter formula in the same sheet as data is in I get correct count which is 7, and if I use same formula in different sheet . Example this formula in Sheet2 `Sheet1!D$2D$13=A17` I get double the count which is 14. – Mowgli Apr 22 '13 at 15:22