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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Re: countifs help requested

Hi and welcome to the forum

=SUMIFS(J4:J168, A4:A168, ">="&DATE(2017, 12, 1), A4:A168, "<="&DATE(2017, 12, 31))
 
Upvote 0
Re: countifs help requested

mrhstn - thank you so much for your help. Have Happy New Year - Best wishes. Jason
 
Upvote 0
Re: countifs help requested

Can I trouble you with another question? I would like to count the number of cells that have a number in them based on the date range 12/1 to 12/31- im not sure what i am doing wrong here.
thanks for you help.

=COUNTIFS(j4:j168,A4:A168,">="&DATE(2017,12,1),A4:A168,"<="&DATE(2017, 12, 31))
 
Upvote 0
Re: countifs help requested

So if you want to count dates in December 2017 then

=COUNTIFS(A4:A168, ">="&DATE(2017, 12, 1), A4:A168, "<="&DATE(2017, 12, 31))

If you also need to test if column J is not blank then

=COUNTIFS(J4:J168, "<>", A4:A168, ">="&DATE(2017, 12, 1), A4:A168, "<="&DATE(2017, 12, 31))

If you need to know if the value in J is a number then

=SUMPRODUCT((A4:A168>=DATE(2017, 12, 1))*(A4:A168<=DATE(2017, 12, 31))*(ISNUMBER(J4:J168)))

HTH
 
Upvote 0
Re: countifs help requested

Sir, may i trouble you with another request for assistance? I found a forum thread where you assisted another with a "countif and" solution. I am trying to accomplish something similar.`I have manipulated the solutions you provided earlier and got stuck .

I am trying to find and sum the values of all '"CO" in H5:H82 for the month of 12/2017 that occur in A5:A82

for instance row 18 - A18(IF the date is 12/ to 12/31) AND H18= "co" THEN add N18, P18,R18,T18,Y18,AA18,AC18,AE18,AK18,AM18,AO18,AQ18
this would calculate all rows within A5:A82

Im sure i am not describing this fluently
this is the formula i came up with:

`=SUMPRODUCT((A5:A82>=DATE(2017, 12,))*A5:A82<=DATE(2017,2,1))*COUNTIF(H5:H82,"co")*ISNUMBER(N5:N82)+ISNUMBER(P5:P82)+ISNUMBER(R5:R82)+ISNUMBER(T5:T82)+ISNUMBER(Y5:Y82)+ISNUMBER(AA5:AA82)+ISNUMBER(AC5:AC82)+ISNUMBER(AE5:AE82)+(ISNUMBER(AK5:AK82)+(ISNUMBER(AM5:AM82)+(ISNUMBER(AO5:AO82)+(ISNUMBER(AQ5:AQ82)))))))))))))))

<tbody>
</tbody>

here is a link to a sample spreadsheet ( drop box)

https://www.dropbox.com/s/fvem5msh5cf6v5u/Copy of 2018 pipeline.xlsx?dl=0

the formula is in cell E114
as you can see the result is 75 where is should be 77,734 ( based on the values in row 18)

thank you again for your help.
 
Upvote 0
Re: countifs help requested

Try this

=SUMPRODUCT(($A$4:$A$82>=DATE(2017, 12, 1))*($A$4:$A$82<=DATE(2017, 12, 31))*($H$4:$H$82="CO")*($M$3:$AQ$3="CUBIC FEET")*($N$4:$AR$82))

As both row 18 and 19 fit your criteria the sum is $533,337.00
 
Upvote 0
Re: countifs help requested

Try this

=SUMPRODUCT(($A$4:$A$82>=DATE(2017, 12, 1))*($A$4:$A$82<=DATE(2017, 12, 31))*($H$4:$H$82="CO")*($M$3:$AQ$3="CUBIC FEET")*($N$4:$AR$82))

As both row 18 and 19 fit your criteria the sum is $533,337.00

Thank you - that works brilliantly,

Q- why if i change the formula to
=SUMPRODUCT(($A$4:$A$82>=DATE(2017, 12, 1))*($A$4:$A$82<=DATE(2017, 12, 31))*($H$4:$H$82="co")*($M$3:$T$3="VERBAL")*($M$4:$T$82))

why would it not return only the sum of the numbers in columns headed with "verbal" from column M:T?

the result is 0 refer to cell E121

https://www.dropbox.com/s/fvem5msh5c...line.xlsx?dl=0
 
Upvote 0
Re: countifs help requested

The formula you made does sum all cells in M4:T82 where the criteria are met (date in column A is december 2017, CO in column H, heading is Verbal)

It would return 0 in the file you gave as your link because no cells have any values to sum.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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