If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula for multiple countifs
Hi,
I would really appreciate anybodys expertise with the following 2 formulas; I have a worksheet that looks a bit like this - (A is days taken to deliver - B is method for delivery) A B 1 sec *I need to be able to count the number of cells in A that contain 1 sig a '1' but only if they have either 'sec','sig' or 'del' in column B. 1 sec 1 man **Then I need to count the number of cells in A that are greater 1 del or equal to '4' but DONT contain 'sec', 'sig' or 'del'. 2 sec 2 sig Note there are several more possible values that turn up in B, 3 sec do I need to let you have all of these too? 3 van 3 sec 3 dup 4 sig 4 man 7 sig |
#2
|
|||
|
|||
Formula for multiple countifs
Hi,
for your 1st question use =SUMPRODUCT((A1:A2=1)*(B1:B2="Sec")*(B1:B2="Sig")* (B1:B2="Del")) to your 2nd =SUMPRODUCT((A1:A21)*(A1:A2=4)*(B1:B2"Sec")*(B 1:B2"Sig")*(B1:B2"Del")) "J.Scargill" wrote: Hi, I would really appreciate anybodys expertise with the following 2 formulas; I have a worksheet that looks a bit like this - (A is days taken to deliver - B is method for delivery) A B 1 sec *I need to be able to count the number of cells in A that contain 1 sig a '1' but only if they have either 'sec','sig' or 'del' in column B. 1 sec 1 man **Then I need to count the number of cells in A that are greater 1 del or equal to '4' but DONT contain 'sec', 'sig' or 'del'. 2 sec 2 sig Note there are several more possible values that turn up in B, 3 sec do I need to let you have all of these too? 3 van 3 sec 3 dup 4 sig 4 man 7 sig Hi |
#3
|
|||
|
|||
Formula for multiple countifs
Hi Eduardo,
Thanks, but the first formula returns a zero. Any ideas why?? There are blank cells in the colums, does that matter?? The actual range i am using is A3:A10000, does this matter?? "Eduardo" wrote: Hi, for your 1st question use =SUMPRODUCT((A1:A2=1)*(B1:B2="Sec")*(B1:B2="Sig")* (B1:B2="Del")) to your 2nd =SUMPRODUCT((A1:A21)*(A1:A2=4)*(B1:B2"Sec")*(B 1:B2"Sig")*(B1:B2"Del")) "J.Scargill" wrote: Hi, I would really appreciate anybodys expertise with the following 2 formulas; I have a worksheet that looks a bit like this - (A is days taken to deliver - B is method for delivery) A B 1 sec *I need to be able to count the number of cells in A that contain 1 sig a '1' but only if they have either 'sec','sig' or 'del' in column B. 1 sec 1 man **Then I need to count the number of cells in A that are greater 1 del or equal to '4' but DONT contain 'sec', 'sig' or 'del'. 2 sec 2 sig Note there are several more possible values that turn up in B, 3 sec do I need to let you have all of these too? 3 van 3 sec 3 dup 4 sig 4 man 7 sig Hi |
#4
|
|||
|
|||
Formula for multiple countifs
Try
'1st =SUMPRODUCT((A1:A100=1)*(B1:B100={"sec","sig","del "})) 'and 2nd =SUMPRODUCT((A1:A1001)*(A1:A100=4)* (ISNA(MATCH(B1:B100,{"sec","sig","del"},0)))) -- Jacob "J.Scargill" wrote: Hi, I would really appreciate anybodys expertise with the following 2 formulas; I have a worksheet that looks a bit like this - (A is days taken to deliver - B is method for delivery) A B 1 sec *I need to be able to count the number of cells in A that contain 1 sig a '1' but only if they have either 'sec','sig' or 'del' in column B. 1 sec 1 man **Then I need to count the number of cells in A that are greater 1 del or equal to '4' but DONT contain 'sec', 'sig' or 'del'. 2 sec 2 sig Note there are several more possible values that turn up in B, 3 sec do I need to let you have all of these too? 3 van 3 sec 3 dup 4 sig 4 man 7 sig |
#5
|
|||
|
|||
Formula for multiple countifs
That works perfectly Jacob, thankyou ever so much.
"Jacob Skaria" wrote: Try '1st =SUMPRODUCT((A1:A100=1)*(B1:B100={"sec","sig","del "})) 'and 2nd =SUMPRODUCT((A1:A1001)*(A1:A100=4)* (ISNA(MATCH(B1:B100,{"sec","sig","del"},0)))) -- Jacob "J.Scargill" wrote: Hi, I would really appreciate anybodys expertise with the following 2 formulas; I have a worksheet that looks a bit like this - (A is days taken to deliver - B is method for delivery) A B 1 sec *I need to be able to count the number of cells in A that contain 1 sig a '1' but only if they have either 'sec','sig' or 'del' in column B. 1 sec 1 man **Then I need to count the number of cells in A that are greater 1 del or equal to '4' but DONT contain 'sec', 'sig' or 'del'. 2 sec 2 sig Note there are several more possible values that turn up in B, 3 sec do I need to let you have all of these too? 3 van 3 sec 3 dup 4 sig 4 man 7 sig |
Thread Tools | |
Display Modes | |
|
|