Remember Me?

#### Menu

#1
December 11th 04, 08:07 PM
 Basil Posts: n/a
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
December 11th 04, 08:29 PM
 Bob Phillips Posts: n/a

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
December 11th 04, 10:41 PM
 RagDyeR Posts: n/a

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
December 12th 04, 08:41 AM
 Harlan Grove Posts: n/a

"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
December 12th 04, 08:46 AM
 Harlan Grove Posts: n/a

"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
January 3rd 17, 08:39 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2017 Posts: 4
sumif function in excel

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM Adam Excel Discussion (Misc queries) 1 December 23rd 04 03:48 PM RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM

All times are GMT +1. The time now is 03:55 PM.

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

# About Us

"It's about Microsoft Excel"

Copyright © 2017