sumifs function

[bcmlau]

=SUMIF(L2:L145,"=02/08/09",D2145)+SUMIF(K2:K145,"=02/08/09",D2145)+SUMIF(J2:J145,"=02/08/09",D2145)+SUMIF(I2:I145,"=02/08/09",D2145)+SUMIF(H2:H145,"=02/08/09",D2145)+SUMIF(G2:G145,"=02/08/09",D2145)+SUMIF(F2:F145,"=02/08/09",D2145)+SUMIF(E2:E145,"=02/08/09",D2145)

Q1: The number is what I what to get from above function. I wonder if there
is another way to make above function work in a simpler form.

Q2: If I want to add all values in column D whick have a date in the cell
range from E2145 within a certain month, how can I correct date from
,"=02/08/09" in to a month range, for instance, in the August month?

 

[John Moore]

Sounds like you need to use COUNTIF for the first part ,,,,, e.g. in cell A1
type =COUNTIF(E2:L145,"02/08/09"), should return the number of times this
date shows up in the range ,,, I assume that is what your asking. For Q2 use
the TEXT function ,,,,, e.g. =TEXT(E2,"mmmm") ,, this should return the month
of any given date.

 

[Jacob Skaria]

Try the below

=SUMPRODUCT((TEXT(E2145,"mm")="08")*D2145)

If this post helps click Yes

 

[bcmlau]

Thank you very much! It works perfectly.

Try the below

=SUMPRODUCT((TEXT(E2145,"mm")="08")*D2145)

If this post helps click Yes