Add up contents of column, if nothing in that column add up contents of other column

Add up contents of column, if nothing in that column add up contents of other column

Post by mahooble » Thu, 20 May 2004 18:47:41


I'll try to explain this dilemma in simple terms:


Code:
--------------------


|---A---|---B---|
|october|novembr|
| beer | beer |
| gin | beer |
| beer | |
| beer | gin |
| gin | |
| water | gin |


--------------------


Basically, say I want to have a total of what was drunk most recently -
so it will look in november, add up whats there, but if there is
nothing in that column it will look down october and use whatever value
is in that column,

Thus I should get the following results:
Beer = 3
Gin = 3


---
Message posted from http://www.yqcomputer.com/
 
 
 

Add up contents of column, if nothing in that column add up contents of other column

Post by Brian » Thu, 20 May 2004 19:34:32

I think this is the basis of what you want.
=IF(COUNTIF(B1:B7,"gin")=0,COUNTIF(A1:A7,"gin"),COUNTIF(B1:B7,"gin"))

Your example of results seems to be wrong. Beer and gin are both i
November - so should =2 surely

 
 
 

Add up contents of column, if nothing in that column add up contents of other column

Post by mahooble » Thu, 20 May 2004 19:39:35

Thanks, but - what I meant is it would look in the most recent month
where something happens, so if there was 'beer' in october and 'beer'
in november, it would ignore october and give a result of beer = 1


---
Message posted from http://www.yqcomputer.com/
 
 
 

Add up contents of column, if nothing in that column add up contents of other column

Post by mahooble » Thu, 20 May 2004 20:48:28

Ah - it doesn't seem to work.

I tried using it in the spreadsheet this is for (which, unfortunatel
has nothing to do with beer) and where something should have returned
result of 2 it returned a result of 1.

I'll explain again using an abbreviated version of the spreadsheet thi
is actually for, just in case my analogy caused confusion:


Code
-------------------


|---- A ----|---- B ----|---- C ----|
| |1st Review |2nd Review |
| Issue 1 | Outcome X | Outcome Y |
| Issue 2 | Outcome Z | |
| Issue 3 | Outcome X | Outcome X |
| Issue 4 | Outcome X | |
| Issue 5 | Outcome Y | |
| Issue 6 | Outcome Z | Outcome X |


-------------------


What I need to find out is - what is the final outcome? However, som
issues have only needed one review, some others needed a second, so
need to get the value in the last column that has something in it.

So, I want it to look down column C. For issue 1 it finds 'Outcome Y'
so 1 point for outcome Y. For issue 2 it finds nothing in column C, s
it then looks in column B and finds 'Outcome Z', so one point fo
Outcome Z.

Thus in the end we should get:

Outcome X = 3
Outcome Y = 2
Outcome Z = 1

I hope this has helped explain what it is I am trying to do, rathe
than confuse things further! :
 
 
 

Add up contents of column, if nothing in that column add up contents of other column

Post by mahooble » Thu, 20 May 2004 22:04:11

^

Sorry to bump this, just i'm in a bit of a desparate situation now an
need to get this done asap!

Thanks :
 
 
 

Add up contents of column, if nothing in that column add up contents of other column

Post by Frank Kabe » Fri, 21 May 2004 01:58:09

Hi
try the following:
=COUNTIF(B1:B100,"beer")+SUMPRODUCT(--(A1:A100="beer"),--(B1:B100=""))