Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / March 2007

Tip: Looking for answers? Try searching our database.

What am I doing wrong with my =sumif ?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Idunno Yet - 06 Mar 2007 20:52 GMT
I need to sum the data in column A that meets criteria in column B "=J".  
I've settled on SUMIF, but it returns zero.  Also, now I need to include the
criteria "blank".  Am I using the right function?  If so, where have I gone
wrong?
       A                B
1     1.7               J
2     5.3               U
3     5.3               U
4     5.3              
5     5.3               U
     =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
Duke Carey - 06 Mar 2007 21:05 GMT
Try

=SUMIF(B1:B5,"J",A1:A5)

> I need to sum the data in column A that meets criteria in column B "=J".  
> I've settled on SUMIF, but it returns zero.  Also, now I need to include the
[quoted text clipped - 7 lines]
> 5     5.3               U
>       =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
Toppers - 06 Mar 2007 21:06 GMT
=SUMIF(B1:B5,"J,"A1:A5)

J is text so needs be placed in double quotes as shown

If you want to include blank (empty) to could use:

=SUMIF(B1:B5,"J",A1:A5)+SUMIF(B1:B5,"",A1:A5)

HTH

> I need to sum the data in column A that meets criteria in column B "=J".  
> I've settled on SUMIF, but it returns zero.  Also, now I need to include the
[quoted text clipped - 7 lines]
> 5     5.3               U
>       =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
Bill Kuunders - 06 Mar 2007 21:10 GMT
=SUMIF(B:B,"J",A:A)+SUMIF(B:B,"",A:A)
or B1:B5 and A1:A5

Signature

Greetings from New Zealand

>I need to sum the data in column A that meets criteria in column B "=J".
> I've settled on SUMIF, but it returns zero.  Also, now I need to include
[quoted text clipped - 9 lines]
> 5     5.3               U
>      =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
Sandy Mann - 06 Mar 2007 21:11 GMT
To make your orijnial formula work enclose the *J* in quotes:

=SUMIF(B1:B7,"J",A1:A7)

To add a test for blank cells use:
=SUMPRODUCT(((B1:B7="J")+(B1:B7=""))*A1:A7)

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>I need to sum the data in column A that meets criteria in column B "=J".
> I've settled on SUMIF, but it returns zero.  Also, now I need to include
[quoted text clipped - 9 lines]
> 5     5.3               U
>      =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
Ragdyer - 07 Mar 2007 01:54 GMT
Another way:

=SUM(SUMIF(B1:B5,{"J",""},A1:A5))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I need to sum the data in column A that meets criteria in column B "=J".
> I've settled on SUMIF, but it returns zero.  Also, now I need to include
[quoted text clipped - 9 lines]
> 5     5.3               U
>      =SUMIF(B1:B5,J,A1:A5)
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.