Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif/sumproduct on multiple criteria
looking for help with following
a1 b1 c1 x r m x s m t r m x r d y r m The problem is I want to count the occurences of m if (a1 = x and b1 = r) and if (a1 = y and b1 =r) the solution I am looking for is ( 2 in the above table) I have about 500 lines of data to process * note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..) any solution would be helpful. |
#2
|
|||
|
|||
Hi Phil
bit confused on the merging comment do you have .........A............B...............C 1....x..............r..................m 2....................s..................m 3....t...............r...................m 4...................r......................d where A1 & A2, A3 & A4 are merged but columns B & C aren't ... in this case i'm confused as to how A3 has t and A4 has x as per your example ..... and i'm not sure an answer is easily possible if the cells aren't merged then the formula would simply be along the lines of =SUMPRODUCT(--(A8:A12="x"),--(B8:B12="r"),--(C8:C12="m")) this formula would work in A1&A2, B1 & B2, C1&C2 are merged but not if A1&A2 are merged and B1, B2, C1, C2 aren't -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Phil.M" wrote in message ... looking for help with following a1 b1 c1 x r m x s m t r m x r d y r m The problem is I want to count the occurences of m if (a1 = x and b1 = r) and if (a1 = y and b1 =r) the solution I am looking for is ( 2 in the above table) I have about 500 lines of data to process * note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..) any solution would be helpful. |
#3
|
|||
|
|||
=SUMPRODUCT(--((A2:A10="x")+(A2:A10="y")0),--(B2:B10="r"),--(C2:C10="m"))
or =SUMPRODUCT((A2:A10={"x","y"})*(B2:B10="r")*(C2:C1 0="m")) I don't know what you mean by merged, if indeed you mean merged as in formatcellsalignment and merge? If so unmerge the cells, remember nothing good comes from merging cells -- Regards, Peo Sjoblom "Phil.M" wrote in message ... looking for help with following a1 b1 c1 x r m x s m t r m x r d y r m The problem is I want to count the occurences of m if (a1 = x and b1 = r) and if (a1 = y and b1 =r) the solution I am looking for is ( 2 in the above table) I have about 500 lines of data to process * note ( range of a1 cell are merged ,ie: a1+a2 a3+a4 ect..) any solution would be helpful. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
sumproduct using multiple criteria | Excel Worksheet Functions | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |