Google Sheets COUNTIF / COUNTIFS formulas based on multiple criteria

google-sheets countif formulas
norburm picture norburm · Jul 30, 2015 · Viewed 58k times · Source

I'm looking for some expertise with formulas. I've got a raw data source and need to be able to summarise it in a separate sheet based on a number of different variables of both number and text formats. I've tried some COUNTIFS, INDEX and other formulas that I've seen published on this site, but it's just not working the way I need it to.

The column headings are:

Timestamp
HRBP (name)
Date
Area name
Team name
Enquiry Type
Enquiry Summary

The summary data I need is: Total number of Enquiry Type by HRBP by Area between specified date range, where I can enter the value of HRBP, Area, and dates in defined cells, and the 'count' of Enquiry Type will be displayed below (and then I can create graphs/charts).

My logic would be something along the lines of:

COUNT [Enquiry Type] IF ([HRBP = 'x'] AND [AREA = 'y'] AND [Date >='z'] AND [DATE <= 'a'])

Can someone please help get to my end goal? Or does anyone know of an App solution that could create this analysis easily enough - the data is entered via a Google Form at front end.

Answer

ZygD picture ZygD · Jul 30, 2015

I think COUNTIFS is what you're looking for. In my dummy data I used this formula:

=countifs(B:B,"a",C:C,"x",D:D,"y",E:E,">=7/30/2015",E:E,"<=7/31/2015")

It's put in the cell G2 in the image: enter image description here

Related questions

How do I combine COUNTIF with OR

In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4. A |…

Read More
Count number of occurrences based on 2 conditions or Regexp

How can I get the number of occurrences for some range based on A regular expression 2+ conditions; let's say cells that contain "yes" and / or "no" What I've got for the moment: COUNTIF(B5:O5; "*yes*") I tried to use …

Read More
iterate row by row COUNTIF using ArrayFormula on Google Sheets

I am trying to iterate a COUNTIF formula that counts the number of cells in each row containing numbers greater than 4. I am trying to use ArrayFormula to do this but it isn't working. Unfortunately it counts ALL the cells …

Read More