Oct 01 2018 10:04 AM
I am trying to use the COUNTIFS, to calculate, a total based on 3 variables.
Column A : "YES"
Column B: Is a date (12-sept-18), that is populated by another formula
Column C: "Close"
I can get the count of Column A and C, but can't figure out how to add B.
=COUNTIFS('2018'!A:A, "YES", '2018'!C:C, "Close")
End result, I want to have it count all entries in a month if they also meet the two above criteria. so I can see a running total for each month...
Hope that make sense...
Oct 01 2018 10:17 AM
Hi Shaun,
It depends on how you define the month for which to calculate. If, for example, for the current month when
=COUNTIFS('2018'!A:A, "YES", '2018'!C:C, "Close",'2018'!B:B,"<="&EOMONTH(TODAY(),0),'2018'!B:B,">"&EOMONTH(TODAY(),-1))
Oct 01 2018 10:21 AM
is there a way to write it for each month individually? so I could have a 12 different cells, populate the date for each respective month?
Oct 01 2018 10:39 AM
Oct 01 2018 10:46 AM
If your months, for example, in cells A1:A12 as 2018-01-15 in A1 (fill Months down) and formatted as mmm (thus you'll see Jan, Feb, etc...) the formula for Jan will be
=COUNTIFS('2018'!A:A, "YES", '2018'!C:C, "Close",'2018'!B:B,"<="&EOMONTH($A1,0),'2018'!B:B,">"&EOMONTH($A1,-1))
and drag it down for other months
Oct 02 2018 03:55 AM
Ok, I've tried the last one, it is seems to work for Jan, but can't seem to get it working for other months (please forgive me, I am a novice with excel). I am going to simplify the formula...
In capture1, is the data (Column B, the date field auto populates, from another formula, with different dates interspersed and all the columns at times contain blanks)
Capture2 is the end result I am hoping for, The part I am having issued with is getting the formula in Cell B1 to "count if Column A is "yes" AND IF the date in column (the entire column) is "Jan" and Cell C1 to count for "Feb" etc...
Oct 02 2018 05:26 AM
Shaun, perhaps due to incorrect using of absolute/relative references. Please check attached.