Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 11th 04, 07:07 PM
Basil
 
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

  #2   Report Post  
Old December 11th 04, 07:29 PM
Bob Phillips
 
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   Report Post  
Old December 11th 04, 09:41 PM
RagDyeR
 
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   Report Post  
Old December 12th 04, 07:41 AM
Harlan Grove
 
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   Report Post  
Old December 12th 04, 07:46 AM
Harlan Grove
 
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 )




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 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM
Access Module coded converted to Excel Function Adam Excel Discussion (Misc queries) 1 December 23rd 04 02:48 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 02:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 01:00 AM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 06:55 PM


All times are GMT +1. The time now is 01:35 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017