countifs

[115shelly]

All,

Something simple i'm sure... but i can't figure it out- I'm trying to sum one column based on the date found in another column. In other words only $ values in column J if the date found in column A is in the month of december.
This is the where I am at.

=countifs(A4:A168,(">="&DATE(2017,12,1))),($J4:$J168)

Thank you for your help.
Jason

 

[mrhstn]

Re: countifs help requested

Hi

So you have two options here, I prefer option 1

=COUNTIFS($N$4:$N$22,"<>",$A$4:$A$22,">="&DATE(2017,12,1),$A$4:$A$22,"<="&DATE(2017,12,31),$H$4:$H$22,"CO")

But you can also do.

=SUMPRODUCT(($N$4:$N$22<>"")*($A$4:$A$22>=DATE(2017,12,1))*($A$4:$A$22<=DATE(2017,12,31))*($H$4:$H$22="CO"))

There is no need to reference the header row as this is a single column. When I did this in #7 it was just an easy pattern to follow as "CUBIC FEET" was offset one column from the columns of interest.

HTH

 

[115shelly]

Re: countifs help requested

thankyou.

this is a bit embarrassing, I am trying to sum the cells M4:M22
`=SUMPRODUCT($M$4:$M$22,"<>",$A$4:$A$22,">="&DATE(2017,12,1),$A$4:$A$22,"<="&DATE(2017,12,31),$H$4:$H$22,"CO")


there's nothing left after this- i promise.

 

[mrhstn]

Re: countifs help requested

I think if it's just one column with those criteria

=SUMIFS($M$4:$M$22,$A$4:$A$22,">="&DATE(2017,12,1),$A$4:$A$22,"<="&DATE(2017,12,31),$H$4:$H$22,"CO")

 

[115shelly]

Re: countifs help requested

Thank you for all of your help.

Please tell me which charity i can donate to in your name ?