Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formulas to count and sum
I am looking for a way to count and sum results in a column based on data
from a range of rows from another worksheet. The data in the sheet1 cells would be a number in a, text in b, text in c, number in d and number in e. Ideally I'm looking for three formulas for sheet2 cells b, c and d. The first formula would count occurrences with these parameters: sheet1 cells a-e would all be a range, i.e. a1:a10, b1:b10 ... e1:e10. i need it to count (not sum) the numbers in e1:e10 when all parts of the formula are true. the formula i'm looking for would be similar to ** if(and('sheet1'!a1:a10=1,('sheet1'!b1:b10="text1", ('sheet1'!c1:c10="text2",('sheet1"!d1:d10='sheet2' !a1)))) The second formula would be a sum of e1:e10 utilizing the same results from the previous formula. The third formula would give me the largest number from e1:e10, again with the same results from the first formula. Basically I want the number in e1 only if these conditions are met: a1=1, b1="text1", c1="text2", d1='sheet2'!a1, same for row2, row3, etc. I have tried count, countif, sumif, etc. but couldn't make them work in the desired way. Any tips on which direction to go will be appreciated greatly. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formulas to count and sum
Hi
1. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1)) 2. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1),'sheet1'!e1:e10) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "mdoyle13" schrieb im Newsbeitrag ... I am looking for a way to count and sum results in a column based on data from a range of rows from another worksheet. The data in the sheet1 cells would be a number in a, text in b, text in c, number in d and number in e. Ideally I'm looking for three formulas for sheet2 cells b, c and d. The first formula would count occurrences with these parameters: sheet1 cells a-e would all be a range, i.e. a1:a10, b1:b10 ... e1:e10. i need it to count (not sum) the numbers in e1:e10 when all parts of the formula are true. the formula i'm looking for would be similar to ** if(and('sheet1'!a1:a10=1,('sheet1'!b1:b10="text1", ('sheet1'!c1:c10="tex t2",('sheet1"!d1:d10='sheet2'!a1)))) The second formula would be a sum of e1:e10 utilizing the same results from the previous formula. The third formula would give me the largest number from e1:e10, again with the same results from the first formula. Basically I want the number in e1 only if these conditions are met: a1=1, b1="text1", c1="text2", d1='sheet2'!a1, same for row2, row3, etc. I have tried count, countif, sumif, etc. but couldn't make them work in the desired way. Any tips on which direction to go will be appreciated greatly. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formulas to count and sum
That sent me in the right direction. I should be able to dramatically cut the
size of my file by not needing as many formulas to get the same result. Thanks for the input. "Frank Kabel" wrote: Hi 1. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1)) 2. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1),'sheet1'!e1:e10) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "mdoyle13" schrieb im Newsbeitrag ... I am looking for a way to count and sum results in a column based on data from a range of rows from another worksheet. The data in the sheet1 cells would be a number in a, text in b, text in c, number in d and number in e. Ideally I'm looking for three formulas for sheet2 cells b, c and d. The first formula would count occurrences with these parameters: sheet1 cells a-e would all be a range, i.e. a1:a10, b1:b10 ... e1:e10. i need it to count (not sum) the numbers in e1:e10 when all parts of the formula are true. the formula i'm looking for would be similar to ** if(and('sheet1'!a1:a10=1,('sheet1'!b1:b10="text1", ('sheet1'!c1:c10="tex t2",('sheet1"!d1:d10='sheet2'!a1)))) The second formula would be a sum of e1:e10 utilizing the same results from the previous formula. The third formula would give me the largest number from e1:e10, again with the same results from the first formula. Basically I want the number in e1 only if these conditions are met: a1=1, b1="text1", c1="text2", d1='sheet2'!a1, same for row2, row3, etc. I have tried count, countif, sumif, etc. but couldn't make them work in the desired way. Any tips on which direction to go will be appreciated greatly. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formulas to count and sum
Sumproduct has worked wonderfully. With a few tweaks it has even worked on
some other data in the file instead of large sections of individual formulas, thus greatly reducing the file size. The one thing I haven't been able to isolate is the largest number in the e1:e10 range when the row sumproduct formulas for a,b,c,d and e are all true. I have tried MAX and LARGE but always the result returned is the largest in that range, i.e. if the row of true statements is 4 (a4,b4...e4) and e4 is 20, but e10 is 24, the result it is giving me is 24 instead of 20. Thanks in advance. "Frank Kabel" wrote: Hi 1. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1)) 2. Try: SUMPRODUCT(--('sheet1'!a1:a10=1),--('sheet1'!b1:b10="text1"),--('sheet1 '!c1:c10="text2"),--('sheet1"!d1:d10='sheet2'!a1),'sheet1'!e1:e10) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "mdoyle13" schrieb im Newsbeitrag ... I am looking for a way to count and sum results in a column based on data from a range of rows from another worksheet. The data in the sheet1 cells would be a number in a, text in b, text in c, number in d and number in e. Ideally I'm looking for three formulas for sheet2 cells b, c and d. The first formula would count occurrences with these parameters: sheet1 cells a-e would all be a range, i.e. a1:a10, b1:b10 ... e1:e10. i need it to count (not sum) the numbers in e1:e10 when all parts of the formula are true. the formula i'm looking for would be similar to ** if(and('sheet1'!a1:a10=1,('sheet1'!b1:b10="text1", ('sheet1'!c1:c10="tex t2",('sheet1"!d1:d10='sheet2'!a1)))) The second formula would be a sum of e1:e10 utilizing the same results from the previous formula. The third formula would give me the largest number from e1:e10, again with the same results from the first formula. Basically I want the number in e1 only if these conditions are met: a1=1, b1="text1", c1="text2", d1='sheet2'!a1, same for row2, row3, etc. I have tried count, countif, sumif, etc. but couldn't make them work in the desired way. Any tips on which direction to go will be appreciated greatly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Formulas | Excel Discussion (Misc queries) | |||
Dynamic Range in Excel that won't count formulas | Excel Discussion (Misc queries) | |||
Count formulas between excel files | Excel Worksheet Functions | |||
Count formulas | Excel Discussion (Misc queries) | |||
Using count formulas with '<=' | Excel Discussion (Misc queries) |