Results 1 to 9 of 9

SUMPRODUCT Formula help

This is a discussion on SUMPRODUCT Formula help within the Excel Questions forums, part of the Question Forums category; Hello All, I want to count the number of cells in Column B that = "A", "B", "C" IF Column ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    17

    Default SUMPRODUCT Formula help

    Hello All,

    I want to count the number of cells in Column B that = "A", "B", "C" IF Column A = "A", "B", "C"

    I can't get a SUMPRODUCT to work with multiple criteria in both columns (or a wildcard in one column).

    I have tried:
    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))
    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="*"))

    Neither work.

    I could use an intermediate calculation, but I'm trying to avoid it. I could use:
    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))+SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="B"))+...

    but that's messy.

    Anyone any ideas? Thanks in advance

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,629

    Default Re: SUMPRODUCT Formula help

    Welcome to the Board.

    In what way doesn't your first formula work?

    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Aug 2004
    Posts
    335

    Default Re: SUMPRODUCT Formula help

    Hi,

    Welcome to board

    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))

    Try removing --

    =SUMPRODUCT((ColB={"A","B","C"})*(ColA={"A","B","C"}))

  4. #4
    New Member
    Join Date
    Feb 2010
    Posts
    17

    Default Re: SUMPRODUCT Formula help

    Hi Andrew,

    When I have only one criterion in my second array, it returns the result I expect.
    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))

    As soon as I add another criterion:
    =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B"}))

    I get the error #N/A 'a value is not available for the formula or function'

    Thanks,
    Ben

  5. #5
    New Member
    Join Date
    Feb 2010
    Posts
    17

    Default Re: SUMPRODUCT Formula help

    Hi Segran,

    The behaviour is the same with or without the --.

    Thank you.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,629

    Default Re: SUMPRODUCT Formula help

    If you have an unequal numnber of conditions:

    =SUMPRODUCT((ColB={"A","B","C"})*((ColA="A")+(ColA="B")))
    Microsoft MVP - Excel

  7. #7
    New Member
    Join Date
    Feb 2010
    Posts
    17

    Default Re: SUMPRODUCT Formula help

    Ah ha!

    Thank you very much indeed - that works a treat.

    Ben

  8. #8
    New Member
    Join Date
    Jan 2010
    Posts
    29

    Default Re: SUMPRODUCT Formula help

    Try this

    aacd
    aacd
    cace
    dbae
    ebae
    fbaa
    gcca

    =IF(AND(COUNTIF(A24:A30,B24)>0,COUNTIF(C24:C30,B24)>0,COUNTIF(D24:D30,B24)>0),COUNTIF(B24:B30,B24))



    Thanx,
    Yuvaraj

  9. #9
    New Member
    Join Date
    Feb 2010
    Posts
    17

    Default Re: SUMPRODUCT Formula help

    Thanks for the reply - I will experiment with that solution too.

    Ben

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com