Array Formula

[Mitch]

I am trying to create a formula as per the excel help "Add numbers based on
multiple conditions" I have typed a similar formula into a spreadsheet and
are getting a #N/A error?
The data that i am using has been exported from SAP into excel, is it
because it may be in a text format? I have tried to convert this using the
text to columns function, but still no joy.
The formula is
=SUM(IF((YTD!$A$5:$A$5989="R02")*(YTD!$H$5:$H$5989="81310110"),YTD!$G$5:$G$5989))

[Max]

Try, array-entered (press CTRL+SHIFT+ENTER):
=SUM(IF((YTD!$A$5:$A$5989="R02")*(YTD!$H$5:$H$5989=81310110),YTD!$G$5:$G$598
9))

(quotes removed from: "81310110")

Alternatively, you could also try (normal ENTER):
=SUMPRODUCT((YTD!$A$5:$A$5989="R02")*(YTD!$H$5:$H$5989=81310110),YTD!$G$5:$G
$5989)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Mitch" <[email protected]> wrote in message
news:[email protected]...
> I am trying to create a formula as per the excel help "Add numbers based
on
> multiple conditions" I have typed a similar formula into a spreadsheet and
> are getting a #N/A error?
> The data that i am using has been exported from SAP into excel, is it
> because it may be in a text format? I have tried to convert this using the
> text to columns function, but still no joy.
> The formula is
>
=SUM(IF((YTD!$A$5:$A$5989="R02")*(YTD!$H$5:$H$5989="81310110"),YTD!$G$5:$G$5
989))

[Brett]

I don't understand why you would be getting the #N/A error specifically,
unless your array arguements had different numbers of rows. The formula you
have shown, however, doesn't have this problem, but maybe double check what
you have in your workbook. Also, from looking at your formula I would assume
that since the values in column H are text, so would be the values in column
G. In that case you would need to replace "YTD!$G$5:$G$5989" with
"VALUE(YTD!$G$5:$G$5989)" in order to get a sum.

"Mitch" wrote:

> I am trying to create a formula as per the excel help "Add numbers based on
> multiple conditions" I have typed a similar formula into a spreadsheet and
> are getting a #N/A error?
> The data that i am using has been exported from SAP into excel, is it
> because it may be in a text format? I have tried to convert this using the
> text to columns function, but still no joy.
> The formula is
> =SUM(IF((YTD!$A$5:$A$5989="R02")*(YTD!$H$5:$H$5989="81310110"),YTD!$G$5:$G$5989))