Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to Count Data (2007)

    I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K

    I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored

    Your assistance will be most appreciated

    I Have Attached both Files. I need the formulas in the File "Summary Data"

    I want to calculate

    1) How many people have been to the Presentation (Col K) based on the criteria for each item in the summary data Col A
    2) How many people have been to the Showhouse (Col L) based on the criteria for each item in the summary data Col A
    3) How Many Negative Response there have been (Col O)-Marked with a "N" based on the criteria for each item in the summary data Col A
    4) How Many Negative Response there have been (Col O)-Marked with a "Y" based on the criteria for each item in the summary data Col A

    Your assistance will be most appreciated

    Regards

    Howard



    Regards

    Howard
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to Count Data (2007)

    I would use:
    B2:
    =COUNTIF([Sample.Data.xls]Combined!$H:$H,A2)
    C2:
    =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
    D2:
    =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$L$2:$L$7000<>""))
    E2:
    =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="N"))
    F2:
    =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="Y"))

    Then copy B2:F2 down the rows
    I presumed that you meant:
    4) How Many <font color=red>Positive</font color=red> Response there have been (Col O)-Marked with a "Y" based on the criteria for each item in the summary data Col A


    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to Count Data (2007)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I think your last 2 formulas should use Column O not M. M is interest. O has the positive or negative feedback...

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Count Data (2007)

    You're correct, thanks. I will correct my previous reply.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Count Data (2007)

    Edited by HansV to correct mistake - thanks to Steve for pointing out my mistake!

    I would make things much easier if you used the same terms in column A on the summary sheet and in column H on the sample data sheet.
    I don't understand requirement 2) - Showhouse is one of the categories and there are no data in column L.
    Neither do I understand requirement 4) - wouldn't Y be positive?

    You could use the following formulas:
    C2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
    E2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="N"))
    F2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="Y"))

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Count Data (2007)

    Hi Hans & Steve

    Thanks for the replies

    Code works perfectly. How can I incorporate a wild card character in the criteria for Eg "*advert*" for eg I want to include Advertisement, Advertorial, Advertising all in one criteria

    Your assistance will be most appreciated

    Regards

    Howard

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Count Data (2007)

    You could use

    =SUMPRODUCT(NOT(ISERROR(SEARCH("advert",[Sample.Data.xls]Combined!$H$2:$H$7000)))*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))

    but the disadvantage is that this can't be filled down.

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Count Data (2007)

    Hi Hans

    If I have the word ending in Showhouse in column H, for eg Weekend Shouse, All Day Showhouse how do I change by formula below i.e =$a2 to include these

    SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))

    Regards

    Howard

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Count Data (2007)

    It would be best to use exactly the same descriptions on both sheets.

    Or you could add an extra column to both sheets that contains uniform abbreviations, e.g. "house" for the examples you mention, and use those in the formulas.

    Or use the SEARCH function as in my previous reply.

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula to Count Data (2007)

    Hi Hans

    Thanks for the help

    Regards

    Howard

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to Count Data (2007)

    To get around the disadvantage tht Hans mentioned with the search, you could add a column (it may be hidden if desired) in each row to list the "search" term. Then this column could be referrred to and the formulas could be copied. This would easier to maintain than hardcoding the search items for each formula.

    Steve

Posting Permissions

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