SUMPRODUCT

[Guest]

I need to create a formula where 2 column criteria must match--Columns A&E
(See Below)

Column A may only have partial information that matches (i.e. it
may show "Netsmart USA" one time and just "Netsmart"the next time--but it
will always show at least "Netsmart").

Column E will always be the same (i.e.,
"53000.5300700001.00300.228200.20070.56995.FMS")
________________________________________________________________
FORMULA
=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200.20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000))
_________________________________________________________________
If Columns A&E BOTH match, I want the total of Column B figures for the
match ONLY. Otherwise the result should be zero.

The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not
just the matched criteria.
HELP!!

 

[Guest]

It looks to me like it should be:

=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000)),--('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200.20070.56995.FMS"),'07-08 Pd. in 07-08-22820'!$B$4:$B$1000)

 

[Bob Phillips]

There is a missing comma

=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200.20070.56995.FMS"))),--('07-08
Pd. in 07-08-22820'!$B$4:$B$1000))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I get 0 as a total, which is incorrect.

 

[Guest]

I get VALUE. When I take the "," out, I a total of ALLof Column B rather
than just the total of the information that has "NETSMART" and
53000.5300700001.0030.228200.20070.56995.FMS
Any suggestions?

Thank you for your help
MO
Albany, NY

There is a missing comma

=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200.20070.56995.FMS"))),--('07-08
Pd. in 07-08-22820'!$B$4:$B$1000))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kigol]

Does the order of columns matter in SUMPRODUCT as it does in LOOKUP
functions? ie: If I had data in Column J to sum, but the two criterion
were in columns K and M, would if affect the outcome at all?

 

[Dave Peterson]

Nope.

=sumproduct(--(sheet2!a1:a10=a1),--(sheet2!c1:c10=c1),sheet2!d1:d10)
should return the same as:
=sumproduct(--(sheet2!c1:c10=c1),--(sheet2!a1:a10=a1),sheet2!d1:d10)
and
=sumproduct(sheet2!d1:d10,--(sheet2!c1:c10=c1),--(sheet2!a1:a10=a1))

(well, if I fixed each formula ok <vbg>.)

 

[Guest]

I don't know. Do you have an alternative for a formula? I need to look at
Columns A&E and if both meet the criteria, I need to add Column B (ONLY the
criteria that meets).

 

[Rick Rothstein \(MVP - VB\)]

Does this formula do what you want?

=SUMPRODUCT(--ISNUMBER((SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000))),--('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.00300.228200.20070.56995.FMS"),'07-08
Pd. in 07-08-22820'!$B$4:$B$1000)

Rick

 

[Guest]

THANK YOU THANK YOU THANK YOU. You have no clue how helpful this is
--
MO
Albany, NY

Does this formula do what you want?

=SUMPRODUCT(--ISNUMBER((SEARCH("NETSMART",'07-08 Pd. in
07-08-22820'!$A$4:$A$1000))),--('07-08 Pd. in
07-08-22820'!$E$4:$E$1000="53000.5300700001.00300.228200.20070.56995.FMS"),'07-08
Pd. in 07-08-22820'!$B$4:$B$1000)

Rick