Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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!!!! |
#5
|
|||
|
|||
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!!!! |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. -- |
#9
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Displayed contents of cell in target row if column criteria is met | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Adding the sum of figures in one column if they meet 2 criteria | Excel Worksheet Functions | |||
Sumproduct - multiple criteria in Column A | Excel Worksheet Functions |