Sunday, August 24, 2014

yogi_Compute Number Of Instances In Specified Months (Date Ranges) And Associated Stats

                      Google Spreadsheet   Post  #1735
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Daniel MIA:
(https://productforums.google.com/forum/#!mydiscussions/docs/zS3M-nshoK8)
Countif falls between and range AND numerous criteria
Hi, thank you for your time in helping me out... I'm completely baffled! Let me try and explain (sorry if it's a bit confusing)...

I have a database type sheet called 'data'. In row 4 is the date, in row 7 there will either be 'Basic' or 'Additional'.

In another sheet, I have broken down into months... column A has the first date of the month, column B has the last date of the month. In column C, I'm trying to count the number of entries in the database that fall on that month AND are classed as 'Basic'. (Then I'll use a similar formula to work out the number of 'Additional' in column D).

So far, I have figured out the following formula will count the total number of entries in the month, but I can't figure how to make a formula also check if it's 'basic' or 'additional' in row 7 of the 'data' sheet:

=COUNT(IFERROR(FILTER('data'!$E$4:$FB$4,'data'!$E$4:$FB$4>=A2,'data'!$E$4:$FB$4<=B2)))

If there are better functions to use, please let me know!

Thank you!!!
Dan

---
Hi, thank you for your time in helping me out... I'm completely baffled! Let me try and explain (sorry if it's a bit confusing)...

I have a database type sheet called 'data'. In row 4 is the date, in row 7 there will either be 'Basic' or 'Additional'.

In another sheet, I have broken down into months... column A has the first date of the month, column B has the last date of the month. In column C, I'm trying to count the number of entries in the database that fall on that month AND are classed as 'Basic'. (Then I'll use a similar formula to work out the number of 'Additional' in column D).

So far, I have figured out the following formula will count the total number of entries in the month, but I can't figure how to make a formula also check if it's 'basic' or 'additional' in row 7 of the 'data' sheet:

=COUNT(IFERROR(FILTER('data'!$E$4:$FB$4,'data'!$E$4:$FB$4>=A2,'data'!$E$4:$FB$4<=B2)))
If there are better functions to use, please let me know!

Than
k you!!!
Dan

------------------------------------------------------------------------------------------------------------------------------------