Home 
Search 
Today's Posts 
#1




SUM/COUNT column(s) based on specific value present within the column
Hello everybody,
I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#2




First. Conditional formatting. Look up 'Highlight cells that meet specific
conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#3




My fault: I wasn't clear enough!
I would like to mix both steps, not just resolve one after another. More concretly: Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex. "abc" Then, there are some calculations that are done dynamically on "Sheet 2" (there are like 200 columns and 300 rows where the data can change). All we know is that in the row 3 we will have the titles of the column listed (that's the data we are looking for!). In our special case we are looking for the column called "abc". So, for the time being, and once all the initial calculations are done, I go to the "sheet 2", find the column where the value in row 3 are equal to "abc" and then make several calculations on the numbers that are below the row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...). Then, on the "Sheet 1", I take the rest of values present in the first column ("A2", "A3", "A4" etc...), and repeat the whole procedure. For my special case we can suppose that there will be always ONLY ONE column named like the values we take from "Sheet 1", however it would be also great to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the values present in all the columns that has "abc" in row 3 (or whatever else we need)). I thought to use INDIRECT formula, but don't now exactly how to go with it in this particular case. Thanks once again, Mark "Bob Phillips" wrote in message ... First. Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#4




Hi Mark,
I think this is what you want =SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25)) =AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25))) =COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25))) all of these are array formulas, so commit with CtrlShiftEnter  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... My fault: I wasn't clear enough! I would like to mix both steps, not just resolve one after another. More concretly: Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex. "abc" Then, there are some calculations that are done dynamically on "Sheet 2" (there are like 200 columns and 300 rows where the data can change). All we know is that in the row 3 we will have the titles of the column listed (that's the data we are looking for!). In our special case we are looking for the column called "abc". So, for the time being, and once all the initial calculations are done, I go to the "sheet 2", find the column where the value in row 3 are equal to "abc" and then make several calculations on the numbers that are below the row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...). Then, on the "Sheet 1", I take the rest of values present in the first column ("A2", "A3", "A4" etc...), and repeat the whole procedure. For my special case we can suppose that there will be always ONLY ONE column named like the values we take from "Sheet 1", however it would be also great to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the values present in all the columns that has "abc" in row 3 (or whatever else we need)). I thought to use INDIRECT formula, but don't now exactly how to go with it in this particular case. Thanks once again, Mark "Bob Phillips" wrote in message ... First. Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#5




You are a wizard Bob!
So, once again, the ARRAY thing was the solution!! Many thanks, Mark "Bob Phillips" wrote in message ... Hi Mark, I think this is what you want =SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25)) =AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25))) =COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25))) all of these are array formulas, so commit with CtrlShiftEnter  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... My fault: I wasn't clear enough! I would like to mix both steps, not just resolve one after another. More concretly: Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex. "abc" Then, there are some calculations that are done dynamically on "Sheet 2" (there are like 200 columns and 300 rows where the data can change). All we know is that in the row 3 we will have the titles of the column listed (that's the data we are looking for!). In our special case we are looking for the column called "abc". So, for the time being, and once all the initial calculations are done, I go to the "sheet 2", find the column where the value in row 3 are equal to "abc" and then make several calculations on the numbers that are below the row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...). Then, on the "Sheet 1", I take the rest of values present in the first column ("A2", "A3", "A4" etc...), and repeat the whole procedure. For my special case we can suppose that there will be always ONLY ONE column named like the values we take from "Sheet 1", however it would be also great to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the values present in all the columns that has "abc" in row 3 (or whatever else we need)). I thought to use INDIRECT formula, but don't now exactly how to go with it in this particular case. Thanks once again, Mark "Bob Phillips" wrote in message ... First. Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#6




Getting back to you Bob once again...
Do you know how to adapt the formulas if we want to avoid #DIV/0! thing? I get this message for all the AVERAGES that the formula is unable to calculate (there is no corresponding value in our row 3...). I thought I can add a supplementary ,"") somewhere at the end (to terminate IF expression), but it's not functionning:( Many thanks, Mark "Bob Phillips" wrote in message ... Hi Mark, I think this is what you want =SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25)) =AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25))) =COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25))) all of these are array formulas, so commit with CtrlShiftEnter  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... My fault: I wasn't clear enough! I would like to mix both steps, not just resolve one after another. More concretly: Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex. "abc" Then, there are some calculations that are done dynamically on "Sheet 2" (there are like 200 columns and 300 rows where the data can change). All we know is that in the row 3 we will have the titles of the column listed (that's the data we are looking for!). In our special case we are looking for the column called "abc". So, for the time being, and once all the initial calculations are done, I go to the "sheet 2", find the column where the value in row 3 are equal to "abc" and then make several calculations on the numbers that are below the row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...). Then, on the "Sheet 1", I take the rest of values present in the first column ("A2", "A3", "A4" etc...), and repeat the whole procedure. For my special case we can suppose that there will be always ONLY ONE column named like the values we take from "Sheet 1", however it would be also great to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the values present in all the columns that has "abc" in row 3 (or whatever else we need)). I thought to use INDIRECT formula, but don't now exactly how to go with it in this particular case. Thanks once again, Mark "Bob Phillips" wrote in message ... First. Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
#7




Mark,
I assume it is just teh AVERAGE that you are having problems with. Therefore, try this =AVERAGE(IF(COUNTIF(Sheet2!B3:M3,A1)0,IF(Sheet2!B 3:M25<"",IF(Sheet2!B3:M3= A1,Sheet2!B3:M25)))) again array entered  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Getting back to you Bob once again... Do you know how to adapt the formulas if we want to avoid #DIV/0! thing? I get this message for all the AVERAGES that the formula is unable to calculate (there is no corresponding value in our row 3...). I thought I can add a supplementary ,"") somewhere at the end (to terminate IF expression), but it's not functionning:( Many thanks, Mark "Bob Phillips" wrote in message ... Hi Mark, I think this is what you want =SUM(IF(Sheet2!B3:M3=A1,Sheet2!B3:M25)) =AVERAGE(IF(Sheet2!B3:M25<"",IF(Sheet2!B3:M3=A1,S heet2!B3:M25))) =COUNT(IF(Sheet2!B3:M3=A1,IF(Sheet2!B3:M25<"",She et2!B3:M25))) all of these are array formulas, so commit with CtrlShiftEnter  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... My fault: I wasn't clear enough! I would like to mix both steps, not just resolve one after another. More concretly: Let's suppose we have on the "Sheet 1", cell "A1" a specific value, f. ex. "abc" Then, there are some calculations that are done dynamically on "Sheet 2" (there are like 200 columns and 300 rows where the data can change). All we know is that in the row 3 we will have the titles of the column listed (that's the data we are looking for!). In our special case we are looking for the column called "abc". So, for the time being, and once all the initial calculations are done, I go to the "sheet 2", find the column where the value in row 3 are equal to "abc" and then make several calculations on the numbers that are below the row 3 (i.e. that are in rows 4 to 300), f. ex. (SUM, COUNT, AVERAGE etc...). Then, on the "Sheet 1", I take the rest of values present in the first column ("A2", "A3", "A4" etc...), and repeat the whole procedure. For my special case we can suppose that there will be always ONLY ONE column named like the values we take from "Sheet 1", however it would be also great to know formulas that can be more flexible (will SUM,COUNT,AVERAGE the values present in all the columns that has "abc" in row 3 (or whatever else we need)). I thought to use INDIRECT formula, but don't now exactly how to go with it in this particular case. Thanks once again, Mark "Bob Phillips" wrote in message ... First. Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in help. Second. Is this just where row 3 equals to value you sum everything in those columns?  HTH RP (remove nothere from the email address if mailing direct) "markx" wrote in message ... Hello everybody, I'm wondering if it could be possible to "tell" Excel (through some formulas, not VBA) to: First: find the columns where the value present in row 3 is equal to a specific variable that we indicate, and then Second: make some operation based on the values in this (or these) column(s) (f. ex. SUM them or COUNT them, or take an AVERAGE of them etc...) Once again, apparently I'm too limited to work it out by myself :). If you were confronted with such a problem or know how to resolve it, I would be grateful for any hints! Thanks in advance! Mark 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I automatically hide columns in a worksheet based on a cell value?  Excel Worksheet Functions  
count number of cells based on TWO conditions (2 different columns  Excel Worksheet Functions  
Tallying columns based on values of 2 different columns  Excel Worksheet Functions  
can you prevent viewing of specific columns in a worksheet  Excel Worksheet Functions  
Hiding columns based on user/password  Excel Worksheet Functions 