# Thread: Formula to Count Data (2007)

1. ## Formula to Count Data (2007)

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

2. ## 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. ## 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. ## Re: Formula to Count Data (2007)

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

5. ## 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. ## 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. ## Re: Formula to Count Data (2007)

You could use

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

8. ## 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. ## 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. ## Re: Formula to Count Data (2007)

Hi Hans

Thanks for the help

Regards

Howard

11. ## 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
•