Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
I am using Excel 2003 and am really stuck on getting a sum or count function
to work if it matches another column. I know this might be bread and butter to some of you but as a bit of a novice, I've tried what I consider to be the obvious and get nothing. My problem - On a sheet B I want to sum (or count) all the times "Telephone" occurs in columns F or G only if "Other" occurs in columns K or L, these columns being on sheet A. See, told you it was simple enough ... but please Please PLEASE how do I do it? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
My sheet A uses 14415 rows, and columns F & G use about 50 variables of which
Telephone is just one, and columns K & L use 21 variables of which Other is just one. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
.... AND I've read my Dummies guide without any success. Does that make me
dummier than dummy? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
Here is what I think I want to do ...
=COUNT(A!F:G,"Telephone")IF(A!K:L,"Other") or =SUM(A!F:G,"Telephone")IF(A!K:L,"Other") but neither of those formula return a value. I know the correct formula will be easy, maybe even easier than these, but I don't know what it is. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
Try the following array formula:
=SUM((--(((SheetA!F1:F10="telephone")+(SheetA!G1:G10="tele phone"))0))*(--(((SheetA!K1:K10="other")+(SheetA!L1:L10="other")) 0))) This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces; Excel puts them there automatically. The formula will not work correctly if you do not enter it with CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 20 May 2010 13:56:01 -0700, Kawboy wrote: I am using Excel 2003 and am really stuck on getting a sum or count function to work if it matches another column. I know this might be bread and butter to some of you but as a bit of a novice, I've tried what I consider to be the obvious and get nothing. My problem - On a sheet B I want to sum (or count) all the times "Telephone" occurs in columns F or G only if "Other" occurs in columns K or L, these columns being on sheet A. See, told you it was simple enough ... but please Please PLEASE how do I do it? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
Chip,
Thank you very Very VERY much. I've loaded your formula and created the array, and am getting totals. With 14415 rows of data, I can't say for sure the totals I'm getting are accurate, but with 14415 rows of data, neither can my boss ;-) Excellent work. Kawboy New Zealand |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 sum if or count if ... or is it something else ???
Hi,
You may try this to count =sumproduct((F2:G14515="Telephone")*(K2:L14515="Ot her")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kawboy" wrote in message ... I am using Excel 2003 and am really stuck on getting a sum or count function to work if it matches another column. I know this might be bread and butter to some of you but as a bit of a novice, I've tried what I consider to be the obvious and get nothing. My problem - On a sheet B I want to sum (or count) all the times "Telephone" occurs in columns F or G only if "Other" occurs in columns K or L, these columns being on sheet A. See, told you it was simple enough ... but please Please PLEASE how do I do it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003: Count total days elapsed | Excel Worksheet Functions | |||
Excel 2003 - Count dates | Excel Discussion (Misc queries) | |||
Excel 2003 Count entries by months | Excel Discussion (Misc queries) | |||
Excel 2003 List with Frequency Count | Excel Discussion (Misc queries) | |||
Count widgets between two dates - Excel 2003 | Excel Discussion (Misc queries) |