Can someone please help me figuring out the Count If formula? I have two tabs in the same workbook in Excel. First tab ("AllRRContributors") lists all reviewers. I need to count the number of review quantity occurrences under "Total Reviews" D column, and if this number is 'Organic' Reviewers (the same I need to do for Email Reviewers and Hybrid Reviewers), then I want to enter this number in the table under "ReviewQ-tyBreakdown" tab.

The AllRRContributors table under 1st tab in the spreadsheet looks like this:

A D KID Total Reviews Campaign CodeReviewer #1 10 OrganicReviewer #2 10 OrganicReviewer #3 52 EmailReviewer #4 52 EmailReviewer #5 33 OrganicReviewer #6 52 EmailReviewer #7 15 EmailReviewer #8 2 HybridReviewer #9 2 OrganiclReviewer #10 40 EmailReviewer #11 52 EmailReviewer #12 63 OrganicReviewer #13 152 OrganicReviewer #14 81 EmailReviewer #15 52 EmailThis table has 500,000 rows.

The ReviewQ-tyBreakdown table under 2nd tab looks like this:

A B C DReview Q-ty OrganicReviewers EmailReviewers HybridReviewers12345678910 21112131415

I need this formula(s) count the number of people who wrote 1 review, 2 reviews, 3 reviews,4 reviews and so on, in first tab and enter this number in appropriate column in the second tab. For example, . there are two reviewers (Reviewer # 1 and Reviewer #2) who wrote 10 reviews each. So number 2 should be entered in the ReviewQ-tyBreakdown table under 2nd tab under "OrganicReviewers" column in respective "Review Q-ty" row, which is 10 reviews.I tried to create the formula below but it didn't work:

=COUNTIF(AllRRContributors!$D2:D510579,"=A3","Organic"), where B2 represents the Review Q-ty of 1 review in OrganicReviewers column.Thank you in advance!

Try these and see how they work: For column B enter the formula:

=SUMPRODUCT(--(AllRRContributors!$D$2:$D$510579=$A2),--(AllRRContributors!$K$2:$K$510579="Organic"))

For column C enter the formula:

=SUMPRODUCT(--(AllRRContributors!$D$2:$D$510579=$A2),--(AllRRContributors!$K$2:$K$510579="Email"))

For column D enter the formula:

=SUMPRODUCT(--(AllRRContributors!$D$2:$D$510579=$A2),--(AllRRContributors!$K$2:$K$510579="Hybrid"))

Then drag them down as many rows as needed.

MIKE

Hi mmcconaghy, The formula works magically! I never used SUMPRODUCT function....Can you please explain how this formula works, and what the two dashes (--)in the formula represent?

Thank you again!

Thank you!!!

Ask Your Question

Weekly Poll

Do you think Marissa Mayer deserves a large severance payout from Yahoo!?

Discuss in The Lounge

Poll History