sumproduct to obtain text output

[EricB]

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

 

[Bernard Liengme]

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

 

[Simon Lloyd]

Your formula should look like this =SUMPRODUCT(--('Ra
Data'!B2:B5000=A2),--('Raw Data'!H2:H5000="P")), what is it you want t
achieve by adding "Payroll", Debit Order"

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

I am getting a #VALUE error

Eric

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[muddan madhu]

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")

 

[Tom Hutchins]

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

 

[Jacob Skaria]

--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