Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Hi,
I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))
If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Hi JMB,
This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
maybe - can you describe how you want to modify the counta function?
=Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Ok I'll try to explain better, I am not very good with words:
I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Sounds like you want to modify the counta function to include a condition
that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Sorry I must be soooo blonde, you already said that in the previous post...
this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
That's okay - I asked for details before taking my shot in the dark. Glad to
hear you got it working. "Tammy" wrote: Sorry I must be soooo blonde, you already said that in the previous post... this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Hi again,
Can I bug you one last time... I have been trying to figure it out with no luck. On one tab your formula works perfect: =Sumproduct(--(August!E1:E65535="DEN"), --(August!I1:I65535="NSW")) On another tab, I change the following formula: =COUNTIF(August!$AU:$AW,"Access Process") and it returns VALUE#? See below what I use. =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) ??? "JMB" wrote: That's okay - I asked for details before taking my shot in the dark. Glad to hear you got it working. "Tammy" wrote: Sorry I must be soooo blonde, you already said that in the previous post... this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
Try:
=Sumproduct((August!E1:E65535="DEN")*(August!$AU1: $AW65535="Access Process")) It's a subtle difference. This version that you tried =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) is a good attempt, but it has two arguments which the sumproduct function will try to multiply together and add up the results. the problem is sumproduct likes its arguments to be the same dimensions, which these are not (first argument is one column and the second argument is three columns). but you should be able to get around it by explicitly multiplying the arrays (ranges) together yourself before sumproduct gets its hands on it. the result of your multiplication gets passed to sumproduct and it should happily add it up (no multiplication necessary on the part of the sumproduct function because only one array gets passed to it). basically, it's a two step process. multiplying the two arrays together and adding up the results. sumproduct is capable of doing both steps if the arrays are the same dimensions. if not, you have to do the multiplication and let sumproduct do the addition only. "Tammy" wrote: Hi again, Can I bug you one last time... I have been trying to figure it out with no luck. On one tab your formula works perfect: =Sumproduct(--(August!E1:E65535="DEN"), --(August!I1:I65535="NSW")) On another tab, I change the following formula: =COUNTIF(August!$AU:$AW,"Access Process") and it returns VALUE#? See below what I use. =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) ??? "JMB" wrote: That's okay - I asked for details before taking my shot in the dark. Glad to hear you got it working. "Tammy" wrote: Sorry I must be soooo blonde, you already said that in the previous post... this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
What can I say, you are an absolute legend...
"JMB" wrote: Try: =Sumproduct((August!E1:E65535="DEN")*(August!$AU1: $AW65535="Access Process")) It's a subtle difference. This version that you tried =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) is a good attempt, but it has two arguments which the sumproduct function will try to multiply together and add up the results. the problem is sumproduct likes its arguments to be the same dimensions, which these are not (first argument is one column and the second argument is three columns). but you should be able to get around it by explicitly multiplying the arrays (ranges) together yourself before sumproduct gets its hands on it. the result of your multiplication gets passed to sumproduct and it should happily add it up (no multiplication necessary on the part of the sumproduct function because only one array gets passed to it). basically, it's a two step process. multiplying the two arrays together and adding up the results. sumproduct is capable of doing both steps if the arrays are the same dimensions. if not, you have to do the multiplication and let sumproduct do the addition only. "Tammy" wrote: Hi again, Can I bug you one last time... I have been trying to figure it out with no luck. On one tab your formula works perfect: =Sumproduct(--(August!E1:E65535="DEN"), --(August!I1:I65535="NSW")) On another tab, I change the following formula: =COUNTIF(August!$AU:$AW,"Access Process") and it returns VALUE#? See below what I use. =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) ??? "JMB" wrote: That's okay - I asked for details before taking my shot in the dark. Glad to hear you got it working. "Tammy" wrote: Sorry I must be soooo blonde, you already said that in the previous post... this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a formula
just passing along knowledge i've gleaned/stolen from some very smart people
who hang out here. "Tammy" wrote: What can I say, you are an absolute legend... "JMB" wrote: Try: =Sumproduct((August!E1:E65535="DEN")*(August!$AU1: $AW65535="Access Process")) It's a subtle difference. This version that you tried =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) is a good attempt, but it has two arguments which the sumproduct function will try to multiply together and add up the results. the problem is sumproduct likes its arguments to be the same dimensions, which these are not (first argument is one column and the second argument is three columns). but you should be able to get around it by explicitly multiplying the arrays (ranges) together yourself before sumproduct gets its hands on it. the result of your multiplication gets passed to sumproduct and it should happily add it up (no multiplication necessary on the part of the sumproduct function because only one array gets passed to it). basically, it's a two step process. multiplying the two arrays together and adding up the results. sumproduct is capable of doing both steps if the arrays are the same dimensions. if not, you have to do the multiplication and let sumproduct do the addition only. "Tammy" wrote: Hi again, Can I bug you one last time... I have been trying to figure it out with no luck. On one tab your formula works perfect: =Sumproduct(--(August!E1:E65535="DEN"), --(August!I1:I65535="NSW")) On another tab, I change the following formula: =COUNTIF(August!$AU:$AW,"Access Process") and it returns VALUE#? See below what I use. =Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access Process")) ??? "JMB" wrote: That's okay - I asked for details before taking my shot in the dark. Glad to hear you got it working. "Tammy" wrote: Sorry I must be soooo blonde, you already said that in the previous post... this works perfect... thanks a million!!! "JMB" wrote: Sounds like you want to modify the counta function to include a condition that "DEN" appears in column E. I think something similar to this should work: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 "Tammy" wrote: Ok I'll try to explain better, I am not very good with words: I have a dashboard with charts and tables feeding off some raw data. I have 2 different types formulas in the dashboard: =COUNTA(August!AT:AV)-3 =COUNTIF(August!I:I,"NSW") I also have some buttons/macros that the user can click to load each month's data. That was simple enough. Now they want to be able to load the data from each month with a limit on only seeing the DEN data which is in column E of the raw data. So I only want each of these to give results of the previous 2 types of formulas if the following is TRUE: EXAMPLE: (August!E:E,"DEN") Does this make sense? I'll try that site too, thanks for that. "JMB" wrote: maybe - can you describe how you want to modify the counta function? =Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3 should yield same results as the counta function if you want to add a condition similar to the previous question: =Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3 should count all cells in AT1:AV100 that are not *empty* and have "DEN" in column E. Note that CountA counts the empty string (ie "") - so you'll need to consider that if you have formulas in column E that return "". Since the two areas differ with respect to the number of columns, you have to multiply the two arrays together yourself to create one array to feed to the sumproduct function. My browser is having problems, so I can't give the exact link. But if you can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a link (bottom left) for Sumproduct (multiple condition tests). It's worth reading. "Tammy" wrote: Hi JMB, This worked perfectly, I did not realise that I also have: =COUNTA(August!AT:AV)-3 The sumproduct does not work for this, I must be doing something wrong, can you assist? Thanks Tammy "JMB" wrote: =Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW")) If you don't have XL 2007, Sumproduct will not accept an entire column, such as E:E, but E1:E65535 would work. "Tammy" wrote: Hi, I have the following formula: =COUNTIF(August!I:I,"NSW") I need help editing this. I only want it to return the values IF: August!E:E,"DEN" Can anyone assist? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|