COUNTIFS is double counting

**infiniticihr** · 08-11-2014, 11:20 AM

Hi, I'm stumped on trying to find the right formula that will only count the first occurrence of a text. I'm using COUNTIFS and so there several criteria in several columns. However one column (E) includes the organization's name and I only want to count the first occurrence for each organization. I tried to count the original total with duplicates but then remove the duplicate occurences. Here is what I did:

=COUNTIFS($G$29:$G$105,"Domestic*",$J$29:$J$105,"Confirmed*",$H$29:$H$105,H18)-COUNTIFS($G$29:$G$105,"Domestic*",$J$29:$J$105,"Confirmed*",$H$29:$H$105,H18,$E$29:$E$105,$E$29:$E$105&"")

But it didn't work... I also tried variations of the SUMPRODUCT and SUM(IF...) without much luck.

Any suggestions?

**ragulduy** · 08-11-2014, 11:39 AM

This would probably be easier using a helper column if that is an option. Then you can change your formula to count only up to that cell, and then count on the helper column. See the attached example

**infiniticihr** · 08-11-2014, 12:20 PM

Hi thanks for the quick reply. I don't think I can make this one work because there are multiple criteria that need to be met first. Once those criteria are applied it may have filtered out the first occurrence of the org's name resulting in the formula capturing a 0. Does that make sense?

**ragulduy** · 08-12-2014, 04:16 AM

Yes, you can use a similar approach with a helper column to count only the first instance of each. If you could upload an example workbook with what the data looks like and the different conditions I can help put a formula together.

**infiniticihr** · 08-12-2014, 11:50 AM

Hi, here is a sample spreadsheet.

I need to have criteria that looks for the following criteria - Domestic, Confirmed and Sector while ensuring it doesn't double count the organizations. I want to know how many partners there are, but some partners are listed more than once. You will see my summary table to the right of the main table and that's where the formulas will provide totals.