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 and welcome to the forum

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

 

[115shelly]

Re: countifs help requested

mrhstn - thank you so much for your help. Have Happy New Year - Best wishes. Jason

 

[115shelly]

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

 

[mrhstn]

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

 

[115shelly]

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:

[TABLE="width: 96"]
<tbody>[TR]
[TD]`=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)))))))))))))))[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[mrhstn]

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

 

[115shelly]

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

 

[mrhstn]

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.

 

[115shelly]

Re: countifs help requested

Alright one last question ( i hope), im almost there...

`=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")*($M$3:$T$3="Open"))

trying to count the number of cells non blank in the range $N$4:$N$22
everything is working nicely this will wrap up the sheet.

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