Excel CountIf Problem

[F4iChic]

Hey all

I am trying to do a countif (or ifs?) in Excel, but I am having no luck

I have multiple columns and want to do a count for example where reason = AGG ANIMAL or AGG PEOPLE

Not all columns have the same values all the time, but where the same value exists across multiple years the value is the same (ABANDON etc)

Trying to do a count of how many cats were surrendered to the shelter over a five year period for the same surrender reason

See attached

HALP

 

[JackTheTripper]

Not all columns have the same values all the time, but where the same value exists across multiple years the value is the same (ABANDON etc)

If you don't know what you're looking for then you can't count it. If one year says ABANDON and another year says GAVE UP then you have to have 2 formulas, one looking for each, then add them up at the end.

Either that or come up with a standard, like all should be ABANDON then manually scan for some words that don't fit the standard, then do a global find and replace (CRTL +h) and replace all GAVE UPs with ABANDON.

 

[F4iChic]

I already stated that the values are the same, as in normalized, but not all values appear in every column, meaning some years there were no cats surrendered as ABANDON

Reading comprehension = Fail

 

[JackTheTripper]

It's your damn accent!

Try vlookup. You didn't put the column letters so I'll assume 2011 is column A.

=VLOOKUP("ABANDON",A:B,2,FALSE)

That will get you what you want for 2011 only. You'll have to do that for each year then add them up.

 

[JackTheTripper]

Or you could do this...

=VLOOKUP("ABANDON",A:B,2,FALSE)+VLOOKUP("ABANDON",D:E,2,FALSE)+VLOOKUP("ABANDON",G:H,2,FALSE)

That should give it to you all in 1 cell.

 

[JackTheTripper]

Nope, that's still not working, cuz if ABANDON isn't there they you get N/A.

This should work better.

=IF(ISNA(VLOOKUP(J2,A:B,2,FALSE)),0,VLOOKUP(J2,A:B,2,FALSE))+IF(ISNA(VLOOKUP(J2,D:E,2,FALSE)),0,VLOOKUP(J2,D:E,2,FALSE))+IF(ISNA(VLOOKUP(J2,G:H,2,FALSE)),0,VLOOKUP(J2,G:H,2,FALSE))

 

[F4iChic]

Oh lord, now you went and got all damned techy on me :rofl

I know the values for each year, example ABANDON in 2011 is 1, 2012 is 0, 2013 is 6 and 2014 is 0

I want something to add them all up for me to tell me 7. And do the same for every other surrender reason

Looks like I'll have to do it on a piece of paper I can't even get Excel to do a SUM and a manual this + that cell

I'm in over my head methinks

 

[JackTheTripper]

Do you only have 2011 to 2014? if so, here you go.

https://drive.google.com/file/d/0B3qNf4sKnkoZakFmZEdEbmpjUUU/view?usp=sharing

Just copy the formula from column N to column N of your spreadsheet and it should just work. Anything you put in column M will be tallied.

Or past this in N2 of your spread sheet.

=IF(ISNA(VLOOKUP(M2,A:B,2,FALSE)),0,VLOOKUP(M2,A:B,2,FALSE))+IF(ISNA(VLOOKUP(M2,D:E,2,FALSE)),0,VLOOKUP(M2,D:E,2,FALSE))+IF(ISNA(VLOOKUP(M2,G:H,2,FALSE)),0,VLOOKUP(M2,G:H,2,FALSE))+IF(ISNA(VLOOKUP(M2,J:K,2,FALSE)),0,VLOOKUP(M2,J:K,2,FALSE))