Need help with using Month as criteria for SUM(COUNTIFS(...
 

Hi all,

I am fairly new to excel and experimenting some functions with my project. I have a SOLD datasheet with Sold Date (column W) in MM/DD/YYYY format). I am trying to count the items with a few criteria using =SUM(COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...criteria_rangeN, criteriaN))

Current formulas are working without any problem.

Now, i want to narrow down by months and found out that MONTH functions does not work with current formulas.

So i tried adding IF function in front of the current formula
Jan=1, Feb=2, ... , Dec = 12

=IF(MONTH(SOLD!W:W)=1, SUM(COUNTIFS(criteria_range1, criteria1, ...criteria_rangeN, criteriaN),0)

Well, that did not work well for all fields. I already have 6 criteria without the date.

Please help!

Need help with using Month as criteria for SUM(COUNTIFS(...
 

On Thu, 22 Mar 2012 20:57:16 +0000, subie2gx wrote:


Hi all,

I am fairly new to excel and experimenting some functions with my
project. I have a SOLD datasheet with Sold Date (column W) in MM/DD/YYYY
format). I am trying to count the items with a few criteria using
=SUM(COUNTIFS(criteria_range1, criteria1, criteria_range2,
criteria2,...criteria_rangeN, criteriaN))

Current formulas are working without any problem.

Now, i want to narrow down by months and found out that MONTH functions
does not work with current formulas.

So i tried adding IF function in front of the current formula
Jan=1, Feb=2, ... , Dec = 12

=IF(MONTH(SOLD!W:W)=1, SUM(COUNTIFS(criteria_range1, criteria1,
..criteria_rangeN, criteriaN),0)

Well, that did not work well for all fields. I already have 6 criteria
without the date.

Please help!

something like:

=sumproduct((criteria_range1 = criteria1)*(criteria_range2 = criteria2) * ... (criteria_rangeN= criteriaN)*(month(criteria_RangeN+1)=MonthNumber))

Just tried it, didn't work but thank you. Any more suggestion?

Need help with using Month as criteria for SUM(COUNTIFS(...
 

On Fri, 23 Mar 2012 16:16:16 +0000, subie2gx wrote:

Just tried it, didn't work but thank you. Any more suggestion?

Obviously, there is something about your data or expectations that I am not understanding from the information you have provided.

I suggest posting a copy of your workbook (with appropriately sanitized data if necessary), on a publically accessible site, such as SkyDrive.Live.com.
Include an example of the results you would like; and make the link available here.