Ask Your Question
0

How to Sum only if range totals greater than 3 [closed]

asked 2014-07-22 20:57:33 +0200

netshark gravatar image

updated 2015-10-26 22:01:01 +0200

Alex Kemp gravatar image

hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.

If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.

I hope this makes sense, any advice is appreciately very much.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-29 23:53:32.810108

1 Answer

Sort by » oldest newest most voted
0

answered 2014-07-22 23:27:36 +0200

m.a.riosv gravatar image

updated 2014-07-23 01:15:12 +0200

If I have understood your question, next a couple of formulas to do it:

=SUMPRODUCT($A$2:$A$6;$B$2:$B$6;$C$2:$C$6)

=COUNTIFS($A$2:$A$6;1;$B$2:$B$6;1;$C$2:$C$6;1)

attached a sample file, CountWithSeveralConditions.ods

There are more options but I think they implies to be introduced as arrays what sometimes is not so easy, while SUMPRODUCT() even working as array doesn't need to be enter as such.

edit flag offensive delete link more

Comments

Thank you for replying, I tried using your example above but not completely understanding it. (Tried to open the file you sent but it isnt opening correctly, and not sure which file there are many xml files inside it, no osd files)

But to clarify what I would like to do is make it so if 3 cells in a row have a number one in them, then count that row as 1. for example =If K16:N16 is equal to or greater than 3 total, then count this row as value of one. And each row will have same formula.

netshark gravatar imagenetshark ( 2014-07-23 23:09:04 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-07-22 20:57:33 +0200

Seen: 495 times

Last updated: Jul 23 '14