| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF variation?
Excel 2003
I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Thanks in advance... Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
One way might be to have a few frozen rows at the top of your sheet, then
use the AutoFilter to sort the data, and use the SUBTOTAL functions at the top of the columns to sum the filtered results. Vaya con Dios, Chuck, CABGx3 "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Thanks in advance... Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
=SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200)
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Thanks in advance... Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
Thanks. One question though. What are the 2 dashes before the
(MONTH(A2:A2000)? Bob "Bob Phillips" wrote in message ... =SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Thanks in advance... Bob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
"Bob Newman" wrote in message
news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Dates are in A1:A10 values to summarize are in B1:B10 the month is 2 (FEB): =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10)) Bruno |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:vy_kg.112403$Ce1.94109@dukeread01... Thanks. One question though. What are the 2 dashes before the (MONTH(A2:A2000)? Bob "Bob Phillips" wrote in message ... =SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Thanks in advance... Bob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
I am having trouble getting it to work, but first of all it looks like from
the description sumproduct multiplies things. Is this correct? I am just trying to add up all the sales for a particular month. Bob "Bruno Campanini" wrote in message ... "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Dates are in A1:A10 values to summarize are in B1:B10 the month is 2 (FEB): =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10)) Bruno |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
The only thing I can think is that the 'date' column has text not dates.
Classically, SUMPRODUCT does multiply arrays, but if you had read that paper I referenced for you, you would have seen how it's usage has been extended. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:KJblg.112425$Ce1.70882@dukeread01... I am having trouble getting it to work, but first of all it looks like from the description sumproduct multiplies things. Is this correct? I am just trying to add up all the sales for a particular month. Bob "Bruno Campanini" wrote in message ... "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Dates are in A1:A10 values to summarize are in B1:B10 the month is 2 (FEB): =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10)) Bruno |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF variation?
I'll study further.
Thanks "Bob Phillips" wrote in message ... The only thing I can think is that the 'date' column has text not dates. Classically, SUMPRODUCT does multiply arrays, but if you had read that paper I referenced for you, you would have seen how it's usage has been extended. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Newman" wrote in message news:KJblg.112425$Ce1.70882@dukeread01... I am having trouble getting it to work, but first of all it looks like from the description sumproduct multiplies things. Is this correct? I am just trying to add up all the sales for a particular month. Bob "Bruno Campanini" wrote in message ... "Bob Newman" wrote in message news:T5_kg.112400$Ce1.43449@dukeread01... Excel 2003 I have a sheet with col. A being a list of dates and the other columns being various figures. I know how to use the sumif function to give me totals for a particular date in col A. How would I do something similar except giving me sums for all figures within a particular month? Dates are in A1:A10 values to summarize are in B1:B10 the month is 2 (FEB): =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10)) Bruno |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
| SUMIF | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
Linear Mode
