Conditional sum based on values in one or more columns

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

I am trying to add the values in col. n if their
corresponding values in col A are not blank.

The following formula is not working, please help.


=SUMPRODUCT((A2:A252<>""),(N2:N252))


Also how will that formula change if I want to sum the
values in N2:N252 if
1. its corresponding values in col a is not blank
2. if the differecence between the corresponding values
in col. D and col E is less than 10.

Please help.
 
For the first part, either use

=SUMPRODUCT(--(A2:A252<>""),(N2:N252))

or

=SUMPRODUCT((A2:A252<>"")*(N2:N252))

To sum if the corresponding values in A *are* blank just change the <> to an =


For the second:-

=SUMPRODUCT((A2:A252<>"")*(ABS((D2:D252)-(E2:E252))<10)*(N2:N252))
 
=SUMPRODUCT((Reaches_P11!A2:A252<>"")*(ABS(Reaches_P11!
N2:N252)))/5280

I tried the above formula and it gives #VALUE error.
Please help.

Thanks
 
Do you by any chance have any text entries in the N2:N252 range - That will kill
it and give you the #VALUE error.
 
Actually I did try to format the cells to number and I am
using function "ABS" to type cast it., still giving me
errror
 
Are you able to mail me a copy of the workbook in question? Please be aware
though that in my Day-Job I work for a company called Lockheed Martin, so just
be sure there is nothing I shouldn't be seeing by virtue of that. You would
need to take the nospam bit out of my email address.
 
Try this instead

=SUMPRODUCT((Reaches_P11!A2:A252<>"")*(ISNUMBER(Reaches_P11!N2:N252)))/5280

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob - Won't that just give a count of the values (Then Divided by 5280) as
opposed to summing them?

Op's original post being:-
 
Hi Ken,

I have just sent the file to your email.

I very much appreciate your help.

thanks
-----Original Message-----
Are you able to mail me a copy of the workbook in question? Please be aware
though that in my Day-Job I work for a company called Lockheed Martin, so just
be sure there is nothing I shouldn't be seeing by virtue of that. You would
need to take the nospam bit out of my email address.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
Seasons Greetings and Very Best wishes to all :-)
--------------------------------------------------------- -------------------



Actually I did try to format the cells to number and I am
using function "ABS" to type cast it., still giving me
errror

-----Original Message-----
Do you by any chance have any text entries in the N2:N252 range - That will kill
it and give you the #VALUE error.
Click to expand...
MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

------------------------------------------------------
Click to expand...
---
-------------------
Seasons Greetings and Very Best wishes to all :-)
------------------------------------------------------
Click to expand...
---
-------------------
=SUMPRODUCT((Reaches_P11!A2:A252<>"")*(ABS (Reaches_P11!
N2:N252)))/5280

I tried the above formula and it gives #VALUE error.
Please help.

Thanks

-----Original Message-----
For the first part, either use

=SUMPRODUCT(--(A2:A252<>""),(N2:N252))

or

=SUMPRODUCT((A2:A252<>"")*(N2:N252))

To sum if the corresponding values in A *are* blank just
change the <> to an =


For the second:-

=SUMPRODUCT((A2:A252<>"")*(ABS((D2:D252)-(E2:E252)) <10)*
(N2:N252))

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------
Click to expand...
---
--- ---
---
-------------------



message
I am trying to add the values in col. n if their
corresponding values in col A are not blank.

The following formula is not working, please help.





Also how will that formula change if I want to
Click to expand...
sum
the
values in N2:N252 if
1. its corresponding values in col a is not blank
2. if the differecence between the corresponding values
in col. D and col E is less than 10.

Please help.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date:
20/12/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003


.
Click to expand...
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003


.
Click to expand...
 
Ken,

You are right, I was just responding to a later post not the original where
the OP had a problem.It's easily resolved though

=SUMPRODUCT((Reaches_P11!A2:A252<>"")*(ISNUMBER(Reaches_P11!N2:N252)),(Reach
es_P11!N2:N252))/5280

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
LOL - You're going to kick yourself over this one (Had me confused for a while
though!!!).

Take a look at each of your formulas that contain a reference to Col O. they
all look like this

=SUMPRODUCT(....*(ABS(Reaches_P11!N2:N254-Reaches_P11!O2-O254)>10))

You screwed up the range reference and have put a '-' in instead of a ':'

Should be as follows:-

=SUMPRODUCT(.....*(ABS(Reaches_P11!N2:N254-Reaches_P11!O2-O254)>10)) NO!!!!!
=SUMPRODUCT(.....*(ABS(Reaches_P11!N2:N254-Reaches_P11!O2:O254)>10)) YES!!
.................................................................................
.....................^^............
 
LOL - See my last post - Had me scratching my head going "What the hell?????"
Reference looked OK at first glance - aaaaaagghhhh!!! :-)
 
Hi Ken,

Thanks for the very valuable help.

I did pull out quite a few hair of mine, trying to
figure out what I did wrong.

One final question., what will that formula look like if
I want to say:

count of rows with col. n = 0 or col n =null or col o = 0
or col o = null and col a is not null and col b is not
null.

thanks a million for your help.



-----Original Message-----
LOL - You're going to kick yourself over this one (Had me confused for a while
though!!!).

Take a look at each of your formulas that contain a reference to Col O. they
all look like this

=SUMPRODUCT(....*(ABS(Reaches_P11!N2:N254-Reaches_P11!O2- O254)>10))

You screwed up the range reference and have put a '-' in instead of a ':'

Should be as follows:-

=SUMPRODUCT(.....*(ABS(Reaches_P11!N2:N254-Reaches_P11! O2-O254)>10)) NO!!!!!
=SUMPRODUCT(.....*(ABS(Reaches_P11!N2:N254-Reaches_P11! O2:O254)>10)) YES!!
......................................................... .........................
.....................^^............

--
Merry Xmas
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
Seasons Greetings and Very Best wishes to all :-)
--------------------------------------------------------- -------------------



Hi Ken,

I have just sent the file to your email.

I very much appreciate your help.

thanks
-----Original Message-----
Are you able to mail me a copy of the workbook in question? Please be aware
though that in my Day-Job I work for a company called Lockheed Martin, so just
be sure there is nothing I shouldn't be seeing by
Click to expand...
virtue
of that. You would
need to take the nospam bit out of my email address.
Click to expand...
MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

------------------------------------------------------
Click to expand...
---
-------------------
Seasons Greetings and Very Best wishes to all :-)
------------------------------------------------------
Click to expand...
---
-------------------
Actually I did try to format the cells to number
Click to expand...
and I
am
using function "ABS" to type cast it., still giving me
errror


-----Original Message-----
Do you by any chance have any text entries in the
N2:N252 range - That will kill
it and give you the #VALUE error.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------
Click to expand...
---
--- ---
---
-------------------





=SUMPRODUCT((Reaches_P11!A2:A252<>"")*(ABS (Reaches_P11!
N2:N252)))/5280

I tried the above formula and it gives #VALUE error.
Please help.

Thanks

-----Original Message-----
For the first part, either use

=SUMPRODUCT(--(A2:A252<>""),(N2:N252))

or

=SUMPRODUCT((A2:A252<>"")*(N2:N252))

To sum if the corresponding values in A *are* blank
just
change the <> to an =


For the second:-

=SUMPRODUCT((A2:A252<>"")*(ABS((D2:D252)- (E2:E252))
<10)*
(N2:N252))

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

------------------------------------------------
Click to expand...
---
--- ---
---
---
-------------------



message
I am trying to add the values in col. n if their
corresponding values in col A are not blank.

The following formula is not working, please help.





Also how will that formula change if I want to sum
the
values in N2:N252 if
1. its corresponding values in col a is not blank
2. if the differecence between the corresponding
values
in col. D and col E is less than 10.

Please help.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date:
20/12/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date:
20/12/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003


.
Click to expand...
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003


.
Click to expand...
 
Try this (I haven't tested the answer to see if it give the right number, so you
need to do that)

=SUMPRODUCT((((Reaches_P11!N2:N254=0)+(Reaches_P11!O2:O254=0))>0)*(Reaches_P11!A
2:A254<>"")*(Reaches_P11!B2:B254<>""))

An empty (null) cell equates to 0 so should be picked up by the =0 argument.
 
Back
Top