Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
I'm trying to create a formula that will count cells that contain and F, but
only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
=sumproduct((a2:a22="network")*(b2:b22="f"))
-- Don Guillett Microsoft MVP Excel SalesAid Software "SBecker" wrote in message ... I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
=SUMPRODUCT(--($A$2:$A$7="Network"),--($B$2:$B$7="F"))
"SBecker" wrote in message ... I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
Hi,
=sumproduct(--(a6:a50="Network")+(B6:B50="F")) "SBecker" wrote: I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
Excellent! Thank you!
"Don Guillett" wrote: =sumproduct((a2:a22="network")*(b2:b22="f")) -- Don Guillett Microsoft MVP Excel SalesAid Software "SBecker" wrote in message ... I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
Thank you!
"Bassman62" wrote: =SUMPRODUCT(--($A$2:$A$7="Network"),--($B$2:$B$7="F")) "SBecker" wrote in message ... I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If/Then formula using ranges
Thanks for your help...
"Eduardo" wrote: Hi, =sumproduct(--(a6:a50="Network")+(B6:B50="F")) "SBecker" wrote: I'm trying to create a formula that will count cells that contain and F, but only if a different column equals a specified word. Example Column A Pass/Fail Network F Strat 1 P Strat 3 P Network F Strat 3 F Strat 1 P So how do I get it to look at all of column A and only count those that equal Network and that column B (pass/fail) equals F? I've tried if(a1:a6="Network",(countif(b1:b6),"F),""). It seems that the problem is the ranges. Any help would be great! Thanks Suzanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
Formula ranges | Excel Worksheet Functions | |||
Date ranges in a sum formula | Excel Worksheet Functions | |||
Formula with ranges | Excel Worksheet Functions | |||
Different ranges in formula | Excel Worksheet Functions |