Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
Hello,
I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
For details on SUMPRODUCT
Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "DocBrown" wrote in message ... Hello, I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="& C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))
Although Excel accepts the formula like that each "middle" range is being ignored. That formula can be written like this and will produce the same result: =SUMIF($J$31:$T$36,C47,$L$31:$V$36) Here is the pattern it's following: Criteria range.....Sum range ............J....................L ............K..................M ............N..................P ............O..................Q ............R...................T ............S...................U ............V..................... If you want to restructure your setup so you can squeeze in 12 months worth of data in the least amount of space then use every other column. C = criteria range S = sum range C...S...C...S...C...S...C...S etc J....K...L...M..N..O...P...Q etc Then the offset in the formula is 1: =SUMIF($J$31:$P$36,C47,$K$31:$Q$36) -- Biff Microsoft Excel MVP "DocBrown" wrote in message ... Hello, I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
Hi Bernard,
This looks promising, but I think I need a bit more hand holding. Example 4 looks similar, but I don't see the whole picture. I'll expand on my concept and see if that changes the answer. The cell layout is: J K L O P Q T U V 31 one text 5 two text 7 four text 9 32 two text 6 three text 8 two text 10 33 34 C74 is the value I'm matching if it = "two" then sumif returns the sum of L32, Q31, V32. Will the SUMPRODUCT be able to do this? Thanks, John "Bernard Liengme" wrote: For details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "DocBrown" wrote in message ... Hello, I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
That is awsome! It works like a charm. I didn't think the ranges would work
like that Thank you a bunch. John "T. Valko" wrote: =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="& C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) Although Excel accepts the formula like that each "middle" range is being ignored. That formula can be written like this and will produce the same result: =SUMIF($J$31:$T$36,C47,$L$31:$V$36) Here is the pattern it's following: Criteria range.....Sum range ............J....................L ............K..................M ............N..................P ............O..................Q ............R...................T ............S...................U ............V..................... If you want to restructure your setup so you can squeeze in 12 months worth of data in the least amount of space then use every other column. C = criteria range S = sum range C...S...C...S...C...S...C...S etc J....K...L...M..N..O...P...Q etc Then the offset in the formula is 1: =SUMIF($J$31:$P$36,C47,$K$31:$Q$36) -- Biff Microsoft Excel MVP "DocBrown" wrote in message ... Hello, I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple critera range and sum_range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "DocBrown" wrote in message ... That is awsome! It works like a charm. I didn't think the ranges would work like that Thank you a bunch. John "T. Valko" wrote: =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="& C47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) Although Excel accepts the formula like that each "middle" range is being ignored. That formula can be written like this and will produce the same result: =SUMIF($J$31:$T$36,C47,$L$31:$V$36) Here is the pattern it's following: Criteria range.....Sum range ............J....................L ............K..................M ............N..................P ............O..................Q ............R...................T ............S...................U ............V..................... If you want to restructure your setup so you can squeeze in 12 months worth of data in the least amount of space then use every other column. C = criteria range S = sum range C...S...C...S...C...S...C...S etc J....K...L...M..N..O...P...Q etc Then the offset in the formula is 1: =SUMIF($J$31:$P$36,C47,$K$31:$Q$36) -- Biff Microsoft Excel MVP "DocBrown" wrote in message ... Hello, I have a worksheet where I'm trying to use the sumif function to sum over multiple criteria ranges and multiple sum_ranges. The following formula, which shows three ranges, (surprisingly) works. Is there a more compact way to implement this? This is for a budget spreadsheet where each of the ranges (J31:J36 for example, are one month. So the full scale formula would have 12 ranges and sum_ranges. Notice that the sum_ranges are offset two columns right of the critera range. =SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36)) If I rearranged the columns, would it that make it possible for a more compact formula to work? Thanks, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with multiple columns in sum_range | Excel Discussion (Misc queries) | |||
Sumif with multiple columns in sum_range | Excel Worksheet Functions | |||
SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |