Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIF full columns, multiple conditions
In column A:A, B:B, i have data. I want a formula to count the number of
times the two pieces of data appaer in the same row. For example if A:A contains 1 2 3 and B:B contains A B C I would like it to count the number of times B appears next to the number 1. I need this done for an entire column, and looking through past helps questions I get the idea that pivot tables would be useful here, however I dont want to use them. Is there any other solution? (In essence its a SUM if formula with two conditions) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIF full columns, multiple conditions
=SUMPRODUCT(--A1:A65535=1),--(B1:B65535="A"))
Note that array function don't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Gupta A." wrote in message ... In column A:A, B:B, i have data. I want a formula to count the number of times the two pieces of data appaer in the same row. For example if A:A contains 1 2 3 and B:B contains A B C I would like it to count the number of times B appears next to the number 1. I need this done for an entire column, and looking through past helps questions I get the idea that pivot tables would be useful here, however I dont want to use them. Is there any other solution? (In essence its a SUM if formula with two conditions) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIF full columns, multiple conditions
You could consider putting a concatentation formula into column C = A1 & B1 in cell C1 You could then count how many times "B1" appears in column C -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=544850 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumIF full columns, multiple conditions
Typo
=SUMPRODUCT(--(A1:A65535=1),--(B1:B65535="A")) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Bob Phillips" wrote in message ... =SUMPRODUCT(--A1:A65535=1),--(B1:B65535="A")) Note that array function don't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Gupta A." wrote in message ... In column A:A, B:B, i have data. I want a formula to count the number of times the two pieces of data appaer in the same row. For example if A:A contains 1 2 3 and B:B contains A B C I would like it to count the number of times B appears next to the number 1. I need this done for an entire column, and looking through past helps questions I get the idea that pivot tables would be useful here, however I dont want to use them. Is there any other solution? (In essence its a SUM if formula with two conditions) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF on multiple sheets | Excel Worksheet Functions | |||
making multiple columns when printing long datasheet | Excel Discussion (Misc queries) | |||
SUMIF and MULTIPLE DATA | Excel Discussion (Misc queries) | |||
Sum(if ... multiple conditions ... Interpretation? | Excel Discussion (Misc queries) |