Need help with a formula.

[Shaun Langille]

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...

[#2]

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))

[#3]

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?

[#4]

that "Data" not date...sorry

[#5]

And the year is the same for all 12 months?

[#6]

yes, all I am hoping for is to simply have 12 cells populate the results for each month.

[#7]

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

[#8]

Why not use a pivot table?

[#9]

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...

[#10]

Shaun, perhaps due to incorrect using of absolute/relative references. Please check attached.