Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003: Count total days elapsed watermt Excel Worksheet Functions 12 June 16th 09 08:12 PM
Excel 2003 - Count dates Arceedee Excel Discussion (Misc queries) 3 January 28th 09 08:09 PM
Excel 2003 Count entries by months Steve2788 Excel Discussion (Misc queries) 2 December 13th 08 06:10 AM
Excel 2003 List with Frequency Count blazingbadger Excel Discussion (Misc queries) 3 May 17th 07 03:34 AM
Count widgets between two dates - Excel 2003 ROC Excel Discussion (Misc queries) 2 March 28th 07 08:06 AM


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"