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 manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History