countifs

115shelly

New Member
Joined
Sep 27, 2015
Messages
19
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
 
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
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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")
 
Upvote 0
Re: countifs help requested

Thank you for all of your help.

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

Forum statistics

Threads
1,223,358
Messages
6,171,625
Members
452,412
Latest member
thomasleysen531

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top