sum column with 2 if criteria
 

=SUMPRODUCT(--('Working Copy'!A3:A1038="WCenterA"),--('Working
Copy'!J3:J1038="1"),'Working Copy'!K3:K1038)

this is the formula I entered as an array and I get an N/A#. I'm trying to
look match 2 criteria and then add the remaining cells in column K.

Instead of "WCenterA" I'd like to use a cell reference.

I figured a combination of If and SumIF formulas, but couldn't get that to
work either! Help!!!

Barbara

SUMPRODUCT is not an array function. It sounds like you have an #N/A in of
'Working Copy'!K3:K1038.

"WCenterA" can easily be replaced by a cell reference

=SUMPRODUCT(--('Working Copy'!A3:A1038="H10),--('Working
Copy'!J3:J1038="1"),'Working Copy'!K3:K1038)




--
HTH

Bob Phillips

"spottkitty" wrote in message
...
=SUMPRODUCT(--('Working Copy'!A3:A1038="WCenterA"),--('Working
Copy'!J3:J1038="1"),'Working Copy'!K3:K1038)

this is the formula I entered as an array and I get an N/A#. I'm trying
to
look match 2 criteria and then add the remaining cells in column K.

Instead of "WCenterA" I'd like to use a cell reference.

I figured a combination of If and SumIF formulas, but couldn't get that to
work either! Help!!!

Barbara

So the #N/A could be due to blanks in the the K column? How could I get it
to ignore the blanks or count them as 0? It should only be counting the
cells with WCenter and 1 which are not blank in K, right?

My formula nowlooks like: =SUMPRODUCT(--('Working
Copy'!A3:A1038="A4"),--('Working Copy'!J3:J1038="1"),'Working Copy'!K3:K1038)


And I still get an N/A result?

Thanks!

"Bob Phillips" wrote:

SUMPRODUCT is not an array function. It sounds like you have an #N/A in of
'Working Copy'!K3:K1038.

"WCenterA" can easily be replaced by a cell reference

=SUMPRODUCT(--('Working Copy'!A3:A1038="H10),--('Working
Copy'!J3:J1038="1"),'Working Copy'!K3:K1038)




--
HTH

Bob Phillips

=SUMIF('Working Copy'!A:A,"="&WC_WPH!A56, 'Working Copy'!K:K)

This formula gave me part of what I'm looking for...it did add up the
numbers! but there are numbers in column K with zeros in column J that I
don't want to add. The 1/0 in J turn on and off as the step is used in a
process.

Ideas? Thanks!!!!

No I do not mean blanks in column K, I mean #N/A values, blanks are ignored
when summing.

You will need SUMPRODUCT if you want to test two conditions, like so

=SUMPRODUCT(--('Working Copy'!A3:A1038=WC_WPH!A56),--('Working
Copy'!J3:J1038=0),'Working Copy'!K3:K1038)


--
HTH

Bob Phillips

"spottkitty" wrote in message
...


=SUMIF('Working Copy'!A:A,"="&WC_WPH!A56, 'Working Copy'!K:K)

This formula gave me part of what I'm looking for...it did add up the
numbers! but there are numbers in column K with zeros in column J that I
don't want to add. The 1/0 in J turn on and off as the step is used in a
process.

Ideas? Thanks!!!!

Thanks for the efforts I copied the formula exactly and I'm still getting an
N/A in my formula cell. I've looked at all 3 columns used in the formula and
there are no #N/A errors

I'm not sure what else to check.



"Bob Phillips" wrote:

No I do not mean blanks in column K, I mean #N/A values, blanks are ignored
when summing.

You will need SUMPRODUCT if you want to test two conditions, like so

=SUMPRODUCT(--('Working Copy'!A3:A1038=WC_WPH!A56),--('Working
Copy'!J3:J1038=0),'Working Copy'!K3:K1038)


--
HTH

Bob Phillips

You could send me the workbook to look at.

--
HTH

Bob Phillips

"spottkitty" wrote in message
...
Thanks for the efforts I copied the formula exactly and I'm still getting
an
N/A in my formula cell. I've looked at all 3 columns used in the formula
and
there are no #N/A errors

I'm not sure what else to check.



"Bob Phillips" wrote:

No I do not mean blanks in column K, I mean #N/A values, blanks are
ignored
when summing.

You will need SUMPRODUCT if you want to test two conditions, like so

=SUMPRODUCT(--('Working Copy'!A3:A1038=WC_WPH!A56),--('Working
Copy'!J3:J1038=0),'Working Copy'!K3:K1038)


--
HTH

Bob Phillips

I clicked on your user name and sent it to the email listed and it came back
undeliverable (notheretiscali address) is that the correct address?

thanks!
Barbara

"Bob Phillips" wrote:

You could send me the workbook to look at.

--

use the email addy without the nothere.

--
HTH

Bob Phillips

"spottkitty" wrote in message
...
I clicked on your user name and sent it to the email listed and it came
back
undeliverable (notheretiscali address) is that the correct address?

thanks!
Barbara

"Bob Phillips" wrote:

You could send me the workbook to look at.

--