sum column with 2 if criteria

[Guest]

=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

 

[Bob Phillips]

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)

 

[Guest]

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!

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

You could send me the workbook to look at.

 

[Guest]

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]

use the email addy without the nothere.