Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Everyone, I've been reading up about this issue recently and it appears that
I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Hi,
DSUM will work against an entire column in all versions: =DSUM(A:B,1,G1:H2) You can set up as complex a criteria range as you want. -- Cheers, Shane Devenshire "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
SUMPRODUCT can use (dynamic) named ranges and is not entered as an array
formula .. just Enter. SUMPRODUCT(--(Range1="Functional"),--(Range2=A3)) Range1 and Range2 are dynamic ranges BUT must be same size. Or simple set to total range (A1:A65500) "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Shane - thanks for the response. I'm going to check out the DSUM formula now
to see if it's going to help me in my quest! Great answer - will hopefully remember to come back on and say thanks if it works :-) "ShaneDevenshire" wrote: Hi, DSUM will work against an entire column in all versions: =DSUM(A:B,1,G1:H2) You can set up as complex a criteria range as you want. -- Cheers, Shane Devenshire "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Toppers - thanks for the reply. I didn't know this about SUMPRODUCT so am
going to give it a go now. I'd only be using one range and inspecting different columns in each part of the criteria so am not sure how this will affect the formula. Again - if I remember (see reply to Shanes answer) I'll log back on and let all know the results of my investigations! Thanks again! "Toppers" wrote: SUMPRODUCT can use (dynamic) named ranges and is not entered as an array formula .. just Enter. SUMPRODUCT(--(Range1="Functional"),--(Range2=A3)) Range1 and Range2 are dynamic ranges BUT must be same size. Or simple set to total range (A1:A65500) "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Just a quick update on this - I've just realised I could setup two different
ranges for the columns I want. They'll both be the same because they're both taken from the same subset of data so I should be okay with this approach as well! "George" wrote: Toppers - thanks for the reply. I didn't know this about SUMPRODUCT so am going to give it a go now. I'd only be using one range and inspecting different columns in each part of the criteria so am not sure how this will affect the formula. Again - if I remember (see reply to Shanes answer) I'll log back on and let all know the results of my investigations! Thanks again! "Toppers" wrote: SUMPRODUCT can use (dynamic) named ranges and is not entered as an array formula .. just Enter. SUMPRODUCT(--(Range1="Functional"),--(Range2=A3)) Range1 and Range2 are dynamic ranges BUT must be same size. Or simple set to total range (A1:A65500) "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Shane - you'll see Toppers' response no doubt. That's the version I've got
working straight away. I'm going to investigate the DSUM function a little more though as it's a lot neater and easier to maintain (I'll hopefully be giving someone else the job of maintaining the sheet) :-) Thanks for the response, but this time Toppers get the Answer as it's working straight away and I unfortunately haven't worked out the DSUM yet :-) "ShaneDevenshire" wrote: Hi, DSUM will work against an entire column in all versions: =DSUM(A:B,1,G1:H2) You can set up as complex a criteria range as you want. -- Cheers, Shane Devenshire "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on Multiple Criteria over whole column
Toppers - thanks for this. Perfect answer to my problem. I knew there must
have been a way around it. I'm still going to investigate the DSUM approach because of the "neatness" of the solution, but you get the Answer this time round! Thanks to both Shane and Toppers who have helped me out enormously! "Toppers" wrote: SUMPRODUCT can use (dynamic) named ranges and is not entered as an array formula .. just Enter. SUMPRODUCT(--(Range1="Functional"),--(Range2=A3)) Range1 and Range2 are dynamic ranges BUT must be same size. Or simple set to total range (A1:A65500) "George" wrote: Everyone, I've been reading up about this issue recently and it appears that I can't use whole columns in any formula I've so far tried. Can someone confirm this for me for definite please? The formulas I've tried so far include the SUMPRODUCT and SUM(IF( as follows: =SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES Allocation'!$E3:$E500=$A3,1,0), 0)) =SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES Allocation'!$E3:$E500=A3)) Both are array formulas and only work when I've specified the range rather than use $A:$A - I've seen somewhere that this limitation is removed from Office 2007 but in other versions these formulas (with columns) return #NUM! *IS* there any method in Office 2003 where I might be able to fudge something together to calculate these two values. I don't really want to set aside another cell to store temporary values so at the moment I think I'm down to using these formulas with cell references as the range :-( Is there a formula that would accept a named range and then still be able to use column references or am I better sticking with the "imperfect" solution I already have :-) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT WITH MULTIPLE CRITERIA | Excel Worksheet Functions | |||
Count Multiple Criteria | Excel Worksheet Functions | |||
COUNT IF - MULTIPLE CRITERIA | Excel Worksheet Functions | |||
Multiple Count Criteria | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions |