Counting if in certain cohort

**sacha.dyer** · 03-12-2014, 10:49 AM

Hi all,
Just the noob again. I have a spreadsheet (which I cant upload from my place of work) which I need some help with. There are 2 pages, one for tracking and another for performance. I am trying to get some information to pull across from the tracking sheet to the performance sheet, and counting how many there are if 2 things are met.
In column E there is a 13 week date which is auto populated depending on the date in column D. Column G works out which cohort period the date in column E falls into from a hidden table. Column H is an end date and then column F shows TRUE or FALSE if the end date is prior to the date in column E. (So sorry if this is already confusing some).
Now what I need on the performance sheet is it to total the number within each cohort but only if the F column shows true.
For instance - There are 4 in May 14 cohort, 3 have met (TRUE) 1 hasnt (FALSE). There are also 4 in June 14 cohort, 1 has met (TRUE) and 3 havent (FALSE). But i only need it to count the 3 TRUE statements from the May cohort, then the June ones will show in the row below.

If you need anymore information I am happy to supply it. But like I say I cant upload the sheet and I know this would be easier.

**etaf** · 03-12-2014, 11:02 AM

you could use a countif() or countifs()

do you have cohort group in the performance sheet ?
so

countifs( cohort group range, cohort criteria in perf sheet, column F, TRUE)

**sacha.dyer** · 03-12-2014, 11:07 AM

What I have in the performance sheet is a table where I want the information to go, so a row for mar 14, apr 14, may 14 etc. In the performance sheet I then have a list where user can select which month they want to look at and it will pull information across to a visible table (the table where all the information is will be hidden and locked so user error/adjustment can not interfere with the results). Will this still work?

**etaf** · 03-12-2014, 11:17 AM

Yes, it works with hidden columns and hidden sheets

Are the dates set out as 1/3/14 and formatted to show MMM YY
or are they text with MAR 14

really depends on the actual data , how/if the count will work OK

so something like
COUNTIF(A1:A100,MONTH(A1:A100)=11)
to count the month

or you may need to use
=COUNTIF(A1:A100,">="&DATE(2014,3,1))-COUNTIF(A1:A100,">"&DATE(2014,3,31))

Using SUMPRODUCT, try

=SUMPRODUCT(--(MONTH(A1:A100)=3),--(YEAR(A1:A100)=2014))

**sacha.dyer** · 03-12-2014, 11:19 AM

yes the date in the formula bar shows 01/03/14 but formatted to look like Mar-14

**etaf** · 03-12-2014, 11:24 AM

so that should work OK then

countifs( cohort group range, cohort criteria in perf sheet, column F, TRUE)

**sacha.dyer** · 03-12-2014, 11:30 AM

I know this may seem a silly thing to say. But I don't have a countifs option in my list of formulas.
I can email the sheet and so might be upload it once I get home. As it won't upload from my email account in my phone!!!
I love excel it works wonders. But only when I know how to use it

**etaf** · 03-12-2014, 11:50 AM

PM sent

as you are on 2003 version, then you would need to use sumproduct()

**sacha.dyer** · 03-13-2014, 06:21 AM

Ok I have done some digging on the internet and have found that i can apply multipul IF functions to the COUNT function. I am trying to apply this formula but keep getting an error messge:
=COUNT(IF(Tracking!G7:G5000=Tracking!AC6))+COUNT(IF(Tracking!F7:F5000=Tracking!AD4))
Any clues anyone?

**etaf** · 03-13-2014, 07:03 AM

i can get this to work
=SUMPRODUCT(--(MONTH(Tracking!G7:G10)=MONTH(H5)),--(YEAR(Tracking!G7:G10)=YEAR(H5)),--(Tracking!F7:F10=TRUE))

BUT NOT when there is a blank in column G as that then returns #value - if i apply ,--(tracking!G7:G10<>"") still not working

I somehow need to ignore the error returned by month() when cell is ""

**sacha.dyer** · 03-13-2014, 07:16 AM

Hi etaf - im guessing you got my email? Im hoping it is easier to see what I mean, as im not great at writing what I mean!
I was trying to see if i could get it to do multipul if functions then counting it. But as you will be able to see from the sheet ive sent you, with the examples ive created in the july table on the performance sheet where im trying to get the information to pull through to it should show a result of 1 being true/met

**etaf** · 03-13-2014, 07:23 AM

yes, been looking at this morning , and as you are on 2003 - cannot use any of the *IFS so a sumproduct would work

works perfect if the range only includes dates , when there are blanks i get #value error

and not been able to resolve

if you change
=IF(ISERROR(VLOOKUP(E7,$AA$6:$AC$30,3)),1/1/1900,VLOOKUP(E7,$AA$6:$AC$30,3))
then it works , as its a date format and not a blank

i have put in F8 , as i was not sure where the count would go

=SUMPRODUCT(--(MONTH(Tracking!G7:G5000)=MONTH(H5)),--(YEAR(Tracking!G7:G5000)=YEAR(H5)),--(Tracking!F7:F5000=TRUE))

the -- is converting true/false to 1/0
so you can then count

testing if the column G cohort is tested for the month and year gains the drop down H5
and so only those months and years that match would be a 1 for each row that matches
and then adding in the TRUE for column F
so we get a count where
month, year and TRUE all match

you could conditional format the 1/1/1900 to have the same font as background so it looks clear

BUT it should be easy to fix the blank dates - but not been able and tried quite a few things for about an hour this morning

i could attach the spreadsheet - BUT not sure of the rules you are under - so would need your permission on the forum reply and i can attach in a reply
Doesn't appear to be any private or sensitive data in the sheet

that may help others here

**sacha.dyer** · 03-13-2014, 07:45 AM

How would i give you permission to upload the file?

**etaf** · 03-13-2014, 07:49 AM

just reply here , saying you give permission

**sacha.dyer** · 03-13-2014, 07:49 AM

Oh - lol
I give you permission

**etaf** · 03-13-2014, 07:53 AM

please find a sample file provided by sacha.dyer , who cannot upload to the forum

i have deleted rows below 40 - to make the files size small and load here as a sample

**etaf** · 03-13-2014, 08:42 AM

i have added the sumproduct into cells
F8,9,10 & 11
I have also changed the vlookup for the cohort to use 1/1/1900 instead of "", and conditional formatted the cells so if 1/1/1900 then the font is white and does not show that date
not my best work - but further research and still cannot get the #value error resolved

Counting if in certain cohort

LinkBack

Thread Tools

Rate This Thread

Display

Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Re: Counting if in certain cohort

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Counting Matching values in two separate ranges without counting duplicates

need help with counting criteria without counting duplicate rows

Counting cells with multiple criteria? Stock/Product counting

Counting Current Streaks in Data/Results (Conditional Counting Formula?)

counting cells with data without counting duplicates

Bookmarks

Bookmarks

Posting Permissions