I am trying to obtain a text answer with following:
=SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"),"Payroll","Debit Order")

I am getting a #VALUE error.

EricB

Perhaps you could explain in words what you want to do
SUMPRODUCT works with numbers
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"EricB" wrote in message
...
I am trying to obtain a text answer with following:
=SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"),"Payroll","Debit Order")

I am getting a #VALUE error.

EricB

B2:B5000 = A2 & H2:H5000 = "P" if both the condition satisfied then
u want "Payroll" , if not "Debit order"

use this formula =IF(B2:B5000=A2,IF(H2:H5000="P","payroll","direct
order"))
not just enter, use Ctrl + Shift + Enter

or u want B2 = A2 and H2 = "P" then get payroll or debit order
B3 = A2 and H3 = "P" then get payroll or debit
order. ????

use this formula =IF(AND(B2=$A$2,H2="P"),"Payroll","Direct order")



On Oct 19, 6:16*pm, EricB wrote:
I am trying to obtain a text answer with following:
=SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"),"Payroll","Debit Order")

I am getting a #VALUE error.

EricB

Try

=IF(SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"))0,"Payroll","Debit Order")

This returns Payroll if ANY of the rows between 2 and 5000 meets both
criteria, or Debit Order if no row meets both criteria.

Hope this helps,

Hutch

"EricB" wrote:

I am trying to obtain a text answer with following:
=SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"),"Payroll","Debit Order")

I am getting a #VALUE error.

EricB

--Are you sure you have a space after the sheet name?

=IF(SUMPRODUCT(('Raw Data'!B2:B5000=A2)*
('Raw Data'!H2:H5000="P")),"Payroll","Debit Order")

If this post helps click Yes
---------------
Jacob Skaria


"EricB" wrote:

I am trying to obtain a text answer with following:
=SUMPRODUCT(--('Raw Data '!B2:B5000=A2),--('Raw Data
'!H2:H5000="P"),"Payroll","Debit Order")

I am getting a #VALUE error.

EricB