12

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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
norburm
  • 121
  • 1
  • 1
  • 3

1 Answers1

10

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

ZygD
  • 22,092
  • 39
  • 79
  • 102
  • 1
    Example above is for Excel, for google sheet use : =countifs(B:B;"a";C:C;"x";D;D;"y";E:E;">=7/30/2015";E:E;"<=7/31/2015") – Richard de Ree Aug 05 '17 at 05:42
  • @Richard What's the difference? – Solomon Ucko Oct 30 '17 at 22:54
  • 3
    @Richard - The screenshot is taken from Google Sheets. As you see, it works. The usage of comas vs. semicolons as separators depends on your locale - for some users it is comas, for others - semicolons. – ZygD Nov 03 '17 at 08:25