Consulting

Results 1 to 6 of 6

Thread: Sumproduct short question - count in sumproduct

  1. #1

    Sumproduct short question - count in sumproduct

    Hi all,
    i have one short question.
    How can i use function COUNT in SUMPRODUCT function?
    is it possible?
    i tried something like this:
    =SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R");COUNTA(Prer!$C$4:$C$1500))
    but it doesnt works
    Till now i use counting in sumproduct with column, where was only number 1 and it counted basics this column.
    It seems like this:
    =SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R");Prer!$Z$4:$Z$1500)
    where column Z was only for help with number 1.

    But this is not the right way in each case.

    how can i fix it?
    thx a lot

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    COUNTA produces a single number, not an array. To multiply by this, take it out of the SUMPRODUCT
    =SUMPRODUCT(--(Prer!$D$4:$D$2000=Prer!$D$3);--(Prer!$M$4:$M$2000<>"C");--(Prer!$R$4:$R$2000="R"))*COUNTA(Prer!$C$4:$C$1500)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    I dont know why, but this return a very big number (2800 instead 9). Not the same as return if i use my old method with help column. Something can be wrong?
    I use sumpruduct and multiply it with counta (this count names in column).

  4. #4
    here is example (atach.)
    in blue is your formula but with wrong result for me and cell next to (yellow) my old formula with right result.
    How can i achive right result but with count and sumproduct formula? or other way, but without help column?
    thx

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    =SUMPRODUCT(--($A$3:$A$1622="R"),--($C$3:$C$1622=100))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    oh god!
    so simply...
    yes, this works
    thank you XLD and
    MDM also thank you for your time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •