0

I'm working out a formula to count the number or cells if the conditions are met using countifs.

I am looking to find out the number of events that are eligible [Data Validation] with a certain [Location], [City], AND [Event].

The example is as such:

Name | Location | City | Event | Event Date | Data Validation

IE: Matt at location 80292 (which includes city(s) 88850, 00000, and "") has 2 [Date Validated] BBQ [Events].

I came up with the following code to count all of these without filters:

=SUM(COUNTIFS(Table2[LOCATION],"80292"*OR(Table2[CITY]="",Table2[CITY]="88850",Table2[CITY]="00000",Table2[EVENT]="BBQ"),Table2[DATE VALIDATE],"1"))

This formula is returning a outstand numbering. Out of 812 Lines of data, it is returning a number of 977. The correct answer should be around 600.

Can someone assist in correcting the formula I have to meet my intent?

Thank you for the help!

Matthew Day
  • 11
  • 2
  • To add a bit more. The Date Validate column returns a 1 or 0 if the date is within a certain area. I use that number to count of the number of 1's for 'good' events. – Matthew Day Jul 20 '18 at 18:07

2 Answers2

0

Any reason why you don't just make a PivotTable out of the data, add slicers on Location, City, Event, add count of event into the Values area, and use the Slicers to show just the things you want to count? Pivots are easier for troubleshooting too...if you get the same answer as now, you can add some other fields into the ROWS area which might help you see what's going on.

If you're unfamiliar with how to use PivotTables, see my answer at VBA to copy data if multiple criteria are met

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
0

One observation :

formula is returning a outstand numbering. Out of 812 Lines of data, it is returning a number of 977."

there is a chance that some of the considered values are NOT in the desired filter conditions eg, " 88850" with a space... use of value() and trim() may help to capture these..

Since you just need the number of occurrence your conditions, sum() is not needed. countifs() or countif() is sufficient.

You defined criteria : "80292"*OR(Table2[CITY]="",Table2[CITY]="88850",Table2[CITY]="00000",Table2[EVENT]="BBQ"

may be separated into : 

= COUNTIFS(Table2[CITY],"",Table2[LOCATION],"80292",Table2[DATE VALIDATE],"1",Table2[EVENT],"BBQ") 
     + COUNTIFS(Table2[CITY],"",Table2[LOCATION],"80292",Table2[DATE VALIDATE],"1",Table2[EVENT],"BBQ")
     + COUNTIFS(Table2[CITY],"88850",Table2[LOCATION],"80292",Table2[DATE VALIDATE],"1",Table2[EVENT],"BBQ")
     + COUNTIFS(Table2[CITY],"00000",Table2[LOCATION],"80292",Table2[DATE VALIDATE],"1",Table2[EVENT],"BBQ")

Hope that helps. (:

p._phidot_
  • 1,913
  • 1
  • 9
  • 17