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

M

mahoobley

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
 
B

BrianB

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
 
M

mahoobley

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
 
M

mahoobley

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! :
 
M

mahoobley

^

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

Thanks :
 
F

Frank Kabel

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