Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
I've been working on this for hours, and I desperately need help.
I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
If you're using DataFilter then you want to use the SUBTOTAL function to ge
the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
Thanks for the response. I should have mentioned, the summary table is on a
different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
I'm trying to do everything with formulas.
So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
The formula is calculating (major progress), but the result is far too low
(about 20% of what it should be). If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong), the result is closer but still not correct. Would this formula omit blank cells? Not all buildings have data in every cell. I need to calculate the average of cells *with data* in BB based upon the selection of rows from AD. I'm totally confused. Your help is most appreciated. Bryan "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
Hey Biff,
I notice you've just reached 10,000 posts in the All-time GG archive for this group - Many Congratulations !! Pete On Nov 13, 10:26*pm, "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)"<BryanakaThePerfection...@discussio ns.microsoft.com wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD * BB 228 * 800.00 450 * 880.00 964 * 870.00 290 * 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
Thanks, Pete!
-- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Hey Biff, I notice you've just reached 10,000 posts in the All-time GG archive for this group - Many Congratulations !! Pete On Nov 13, 10:26 pm, "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)"<BryanakaThePerfection...@discussio ns.microsoft.com wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
Would this formula omit blank cells?
Not all buildings have data in every cell. That could lead to incorrect results if you had something like this: 228 800.00 450 880.00 964 870.00 290 290 has a corresponding empty cell so that cell is evaluated as 0 and is included in the average. To account for that (still array entered): A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1)*(Sheet2!BB1:BB10<""),Sheet2!BB1:BB10)) If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong) No, you don't want to do that! We're using "*" to multiply the arrays together and we'll get a result of either 1 or 0. Where all 3 conditions are TRUE the array multiplication will return a 1 and where the array multiplication =1 it includes the corresponding cell from BB in the average. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... The formula is calculating (major progress), but the result is far too low (about 20% of what it should be). If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong), the result is closer but still not correct. Would this formula omit blank cells? Not all buildings have data in every cell. I need to calculate the average of cells *with data* in BB based upon the selection of rows from AD. I'm totally confused. Your help is most appreciated. Bryan "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
That was it!! Biff saves the day again!
A million thanks -- you have no idea how grateful I am. "T. Valko" wrote: Would this formula omit blank cells? Not all buildings have data in every cell. That could lead to incorrect results if you had something like this: 228 800.00 450 880.00 964 870.00 290 290 has a corresponding empty cell so that cell is evaluated as 0 and is included in the average. To account for that (still array entered): A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1)*(Sheet2!BB1:BB10<""),Sheet2!BB1:BB10)) If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong) No, you don't want to do that! We're using "*" to multiply the arrays together and we'll get a result of either 1 or 0. Where all 3 conditions are TRUE the array multiplication will return a 1 and where the array multiplication =1 it includes the corresponding cell from BB in the average. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... The formula is calculating (major progress), but the result is far too low (about 20% of what it should be). If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong), the result is closer but still not correct. Would this formula omit blank cells? Not all buildings have data in every cell. I need to calculate the average of cells *with data* in BB based upon the selection of rows from AD. I'm totally confused. Your help is most appreciated. Bryan "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help averaging a range using a diff column as criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... That was it!! Biff saves the day again! A million thanks -- you have no idea how grateful I am. "T. Valko" wrote: Would this formula omit blank cells? Not all buildings have data in every cell. That could lead to incorrect results if you had something like this: 228 800.00 450 880.00 964 870.00 290 290 has a corresponding empty cell so that cell is evaluated as 0 and is included in the average. To account for that (still array entered): A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1)*(Sheet2!BB1:BB10<""),Sheet2!BB1:BB10)) If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong) No, you don't want to do that! We're using "*" to multiply the arrays together and we'll get a result of either 1 or 0. Where all 3 conditions are TRUE the array multiplication will return a 1 and where the array multiplication =1 it includes the corresponding cell from BB in the average. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... The formula is calculating (major progress), but the result is far too low (about 20% of what it should be). If I replace the "*" in your formula with a "-" (which makes more sense to me but could be wrong), the result is closer but still not correct. Would this formula omit blank cells? Not all buildings have data in every cell. I need to calculate the average of cells *with data* in BB based upon the selection of rows from AD. I'm totally confused. Your help is most appreciated. Bryan "T. Valko" wrote: I'm trying to do everything with formulas. So that means you're not using DataFilter? Not a problem! To average the rent on bldg's with =100 units and <=299 units: Array entered** : =AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10)) Better to use cells to hold the criteria: A1 = 100 B1 = 299 =AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" m wrote in message ... Thanks for the response. I should have mentioned, the summary table is on a different worksheet than the raw data, so I'm trying to do everything with formulas. "T. Valko" wrote: If you're using DataFilter then you want to use the SUBTOTAL function to ge the average of the visible rows. If you're filtering on column AD then: =SUBTOTAL(1, BB2:BB100) 1 is the index number for average. See Excel help on SUBTOTAL for other index numbers and what they mean. Just make sure you put the formula outside of the filtered rows. It's usually a good idea to put the formula above the filter. -- Biff Microsoft Excel MVP "Bryan (aka The Perfectionist)" <Bryan (aka The wrote in message ... I've been working on this for hours, and I desperately need help. I'm simplifying the spreadsheet immensely, but the heart of my problem is this: In my spreadsheet of apartment buildings, the # of apartments in a building is in column AD, and rent per apt. unit is in column BB: AD BB 228 800.00 450 880.00 964 870.00 290 760.00 I've been asked to create a summary table which calculates the average rent per unit for various ranges -- for instance, the average rent per unit for buildings with 100 to 299 apartments, 300 to 499 apartments, etc. So I need to filter rows in column AD to show just the buildings within a range (100-299 apartments, for example), then sum column BB for only those AD-filtered rows, then average column BB for only the AD-filtered rows. I've read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF, and SUMPRODUCT, and I'm just not getting there. Please save me!!! A million thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a range based on criteria in rowdata and column name | Excel Worksheet Functions | |||
COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
averaging based on several criteria | Excel Discussion (Misc queries) | |||
Variable range column summation and averaging | Excel Discussion (Misc queries) | |||
Countif Criteria (2 diff columns) | Excel Discussion (Misc queries) |