Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF FORMULA ISSUES - NEED FORMULA CORRECTED
I can not figure out what formula to use.I need to count the number of times
a certain word "Help Desk" comes up in Column G if the vlaue in Column B is "1". I think I should use the count if statement but it does not seem to work. Here is the formula I tried: =COUNTIF('FP Export data'!$G$2:$G$11000,"Help Desk" & 'FP Export data'!$B$2:$B$11000,"1") FP Export data shows info as: A Column B Column G Column Lee 1 Help Desk What I am trying to accomplish is search the B and G columns and count all instances where Column B=1 and Column G=HelpDesk. What did I do wrong?. I get the error "you entered too many arguments for this function." |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF FORMULA ISSUES - NEED FORMULA CORRECTED
When you have multiple criteria use SUMPRODUCT()
=SUMPRODUCT((B1:B10=1)*(G1:G10="Help Desk")) =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)) If you are using 2007 check out help on COUNTIFS() If this post helps click Yes --------------- Jacob Skaria "Debbi" wrote: I can not figure out what formula to use.I need to count the number of times a certain word "Help Desk" comes up in Column G if the vlaue in Column B is "1". I think I should use the count if statement but it does not seem to work. Here is the formula I tried: =COUNTIF('FP Export data'!$G$2:$G$11000,"Help Desk" & 'FP Export data'!$B$2:$B$11000,"1") FP Export data shows info as: A Column B Column G Column Lee 1 Help Desk What I am trying to accomplish is search the B and G columns and count all instances where Column B=1 and Column G=HelpDesk. What did I do wrong?. I get the error "you entered too many arguments for this function." |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF FORMULA ISSUES - NEED FORMULA CORRECTED
Try this (all on one line)...
=SUMPRODUCT(--('FP Export data'!$G$2:$G$11000="Help Desk"), --('FP Export data'!$B$2:$B$11000=1)) Better to use cells to hold the criteria... I2 = Help Desk J2 = 1 =SUMPRODUCT(--('FP Export data'!$G$2:$G$11000=I2), --('FP Export data'!$B$2:$B$11000=J2)) -- Biff Microsoft Excel MVP "Debbi" wrote in message ... I can not figure out what formula to use.I need to count the number of times a certain word "Help Desk" comes up in Column G if the vlaue in Column B is "1". I think I should use the count if statement but it does not seem to work. Here is the formula I tried: =COUNTIF('FP Export data'!$G$2:$G$11000,"Help Desk" & 'FP Export data'!$B$2:$B$11000,"1") FP Export data shows info as: A Column B Column G Column Lee 1 Help Desk What I am trying to accomplish is search the B and G columns and count all instances where Column B=1 and Column G=HelpDesk. What did I do wrong?. I get the error "you entered too many arguments for this function." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Issues | Excel Worksheet Functions | |||
i corrected a formula, need go back to worksheet | New Users to Excel | |||
Formula issues | Excel Worksheet Functions | |||
I'm having formula issues | Excel Discussion (Misc queries) | |||
Array formula reference (Corrected) | Excel Discussion (Misc queries) |