Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs function
=SUMIF(L2:L145,"=02/08/09",D2:D145)+SUMIF(K2:K145,"=02/08/09",D2:D145)+SUMIF(J2:J145,"=02/08/09",D2:D145)+SUMIF(I2:I145,"=02/08/09",D2:D145)+SUMIF(H2:H145,"=02/08/09",D2:D145)+SUMIF(G2:G145,"=02/08/09",D2:D145)+SUMIF(F2:F145,"=02/08/09",D2:D145)+SUMIF(E2:E145,"=02/08/09",D2:D145)
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 E2:P145 within a certain month, how can I correct date from ,"=02/08/09" in to a month range, for instance, in the August month? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs function
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. "bcmlau" wrote: =SUMIF(L2:L145,"=02/08/09",D2:D145)+SUMIF(K2:K145,"=02/08/09",D2:D145)+SUMIF(J2:J145,"=02/08/09",D2:D145)+SUMIF(I2:I145,"=02/08/09",D2:D145)+SUMIF(H2:H145,"=02/08/09",D2:D145)+SUMIF(G2:G145,"=02/08/09",D2:D145)+SUMIF(F2:F145,"=02/08/09",D2:D145)+SUMIF(E2:E145,"=02/08/09",D2:D145) 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 E2:P145 within a certain month, how can I correct date from ,"=02/08/09" in to a month range, for instance, in the August month? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs function
Try the below
=SUMPRODUCT((TEXT(E2:P145,"mm")="08")*D2:D145) If this post helps click Yes --------------- Jacob Skaria "bcmlau" wrote: =SUMIF(L2:L145,"=02/08/09",D2:D145)+SUMIF(K2:K145,"=02/08/09",D2:D145)+SUMIF(J2:J145,"=02/08/09",D2:D145)+SUMIF(I2:I145,"=02/08/09",D2:D145)+SUMIF(H2:H145,"=02/08/09",D2:D145)+SUMIF(G2:G145,"=02/08/09",D2:D145)+SUMIF(F2:F145,"=02/08/09",D2:D145)+SUMIF(E2:E145,"=02/08/09",D2:D145) 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 E2:P145 within a certain month, how can I correct date from ,"=02/08/09" in to a month range, for instance, in the August month? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs function
Thank you very much! It works perfectly.
"Jacob Skaria" wrote: Try the below =SUMPRODUCT((TEXT(E2:P145,"mm")="08")*D2:D145) If this post helps click Yes --------------- Jacob Skaria "bcmlau" wrote: =SUMIF(L2:L145,"=02/08/09",D2:D145)+SUMIF(K2:K145,"=02/08/09",D2:D145)+SUMIF(J2:J145,"=02/08/09",D2:D145)+SUMIF(I2:I145,"=02/08/09",D2:D145)+SUMIF(H2:H145,"=02/08/09",D2:D145)+SUMIF(G2:G145,"=02/08/09",D2:D145)+SUMIF(F2:F145,"=02/08/09",D2:D145)+SUMIF(E2:E145,"=02/08/09",D2:D145) 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 E2:P145 within a certain month, how can I correct date from ,"=02/08/09" in to a month range, for instance, in the August month? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumifs function | Excel Worksheet Functions | |||
Sumifs & Not Like | Excel Discussion (Misc queries) | |||
SUMIFS function help | Excel Discussion (Misc queries) | |||
Can SUMIFS use the OR function? | Excel Worksheet Functions | |||
[Excel 2007 Beta2] Function SUMIFS | Excel Worksheet Functions |