A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

sumif function in excel



 
 
Thread Tools Display Modes
  #1  
Old December 11th 04, 08:07 PM
Basil
external usenet poster
 
Posts: n/a
Default sumif function in excel

Hi, I need assistance with a download that has text in two seperate columns,
referencing general ledger account numbers. I need to reference these
columns in a sumif formula, but can't seem to get it right.
This is an example:
sumif(download worksheet a1:b200,"'610'"&"'00'",downloadworksheet d1:d200)

Any ideas?
Thanks
Ads
  #2  
Old December 11th 04, 08:29 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

If you are trying to test for 2 values, you need 2 SUMIFs

=SUMIF('download worksheet'!,A1:B200,"610",'download
worksheet'!D1200)+SUMIF('download worksheet'!,A1:B200,"00,'download
worksheet'!D1200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Basil" > wrote in message
...
> Hi, I need assistance with a download that has text in two seperate

columns,
> referencing general ledger account numbers. I need to reference these
> columns in a sumif formula, but can't seem to get it right.
> This is an example:
> sumif(download worksheet a1:b200,"'610'"&"'00'",downloadworksheet d1:d200)
>
> Any ideas?
> Thanks



  #3  
Old December 11th 04, 10:41 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

I think you'll have a problem trying to use SumIf() to search *2* columns
(A1:B200).
If "610" and "00" are each in separate columns, (610 in A and 00 in B), then
you'll need 2 "joined" SumIf's:

=SUMIF(A1:A200,"610",D1200)+SUMIF(B1:B200,"00",D 1200)

If, on the other hand, "610" and "00" are contained in *both* columns, then
you could try something like this:

=SUM(SUMIF(A1:A200,{"610","00"},D1200)+SUMIF(B1: B200,{"610","00"},D1200)
)

The caveat for both formulas however, is that if "610" and/or "00" are in
the same row, the value in Column D will be totaled *twice*.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Basil" > wrote in message
...
Hi, I need assistance with a download that has text in two seperate columns,
referencing general ledger account numbers. I need to reference these
columns in a sumif formula, but can't seem to get it right.
This is an example:
sumif(download worksheet a1:b200,"'610'"&"'00'",downloadworksheet d1:d200)

Any ideas?
Thanks


  #4  
Old December 12th 04, 08:41 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

"Bob Phillips" > wrote...
>If you are trying to test for 2 values, you need 2 SUMIFs
>
>=SUMIF('download worksheet'!,A1:B200,"610",'download worksheet'!D1200)
>+SUMIF('download worksheet'!,A1:B200,"00,'download worksheet'!D1200)

....

Multiple syntax errors, so untested.

Excel's formula parser chokes in the commas immediately after the
exclamation points and the missing double quote after "00.


  #5  
Old December 12th 04, 08:46 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

"RagDyeR" > wrote...
>I think you'll have a problem trying to use SumIf() to search *2* columns
>(A1:B200). If "610" and "00" are each in separate columns, (610 in A and 00
>in B), then you'll need 2 "joined" SumIf's:
>
>=SUMIF(A1:A200,"610",D1200)+SUMIF(B1:B200,"00", D1200)

....
>The caveat for both formulas however, is that if "610" and/or "00" are in
>the same row, the value in Column D will be totaled *twice*.

....

Which is why the formula above is inadequate. You're also assuming the OP
wants one criterion *OR* the other satisfied. If so, and if they apply
separately to each column, then the OP needs to use something like

=SUMPRODUCT(--((A1:A200="610")+(B1:B200="00")>0),D1200)

On the other hand, if the OP's '&' in his original attempt should be taken
to mean *BOTH* criteria must be satisfied, then the OP needs to use

=SUMPRODUCT((A1:A200="610")*(B1:B200="00"),D1200 )


  #6  
Old January 3rd 17, 08:39 AM posted to microsoft.public.excel.worksheet.functions
maryjhon830@gmail.com
external usenet poster
 
Posts: 4
Default sumif function in excel

http://tutorialway.com/use-sumif-function-in-excel/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM
Access Module coded converted to Excel Function Adam Excel Discussion (Misc queries) 1 December 23rd 04 03:48 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM


All times are GMT +1. The time now is 06:45 PM.


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