I have a spreadsheet showing data for each state, by year (in long format), like so:
STATE YEAR COUNT
AL 2000 5
AL 2001 ---
AL 2002 4
AK 2000 8
AK 2001 2
AK 2002 3
etc.
There is always the same 3 years of data for every State. For each State, I'd like to count across the 3 rows of data and if any value in Column C is equal to "---", then return a 0 in Column D for all 3 rows. Otherwise, return a 1.
For example:
STATE YEAR COUNT RESULT
AL 2000 5 0
AL 2001 --- 0
AL 2002 4 0
AK 2000 8 1
AK 2001 2 1
AK 2002 3 1
etc.
I tried using the CountIF function, but couldn't figure out how to tell Excel to apply it separately for every 3 rows of data. Any ideas? Thank you.
Last edited by romperstomper; 07-08-2011 at 05:25 PM. Reason: Mark solved
How about this:
Cheers,=IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10="---")),0,SUMIF($A$2:$A$10,A2,$C$2:$C$10))
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
If you did a find and replace "-" for 0 then you could do:-
The formula is exactly the same for each cell, but you cannot copy and paste it from D1 to D2 and D3 because it will increment the references as you do.CELL D1 IF((C1*C2*C3)>0,1,0)
CELL D2 IF((C1*C2*C3)>0,1,0)
CELL D3 IF((C1*C2*C3)>0,1,0)
Now copy these three cells and paste to the end of your data.
Hope this is what your looking for.
Last edited by col12345; 03-25-2011 at 03:28 PM. Reason: rule 3 error
That worked great. I didn't need to create a sum when the COUNT = "---", so I changed the end of the function to simply ... ,0,1).Originally Posted by ConneXionLost
Also, I'm curious: What does the "--" prefix do?
It seems I can use a similar approach for a similar type of calculation. Now that I've flagged every state with a 1 or a 0, I'd like to sum the values for YEAR, only for those states who have a 1 for the Result.
So go from this:
STATE YEAR COUNT RESULT
AL 2000 5 0
AL 2001 --- 0
AL 2002 4 0
AK 2000 8 1
AK 2001 2 1
AK 2002 3 1
etc.
To this:
STATE YEAR COUNT RESULT
AL 2000 5 0
AL 2001 --- 0
AL 2002 4 0
AK 2000 8 1
AK 2001 2 1
AK 2002 3 1
TOT 2000 8
TOT 2001 2
TOT 2002 3
(Not the best example since there's only one State that applies, but I think you get the idea.)
I tried using this function:
But it's summing all of the States; not just those who have a flag of 1. Any suggestions?=IF(SUMPRODUCT(--($B$2:$B$10=B2),--($D$2:$D$10=1)),SUMIF($B$2:$B$10,B2,$C$2:$C$10))
Thank you again. This is great.
Figured it out:
Looks like I don't need that last SUMIF clause (the extra B2:B10,B2), nor do I need the first IF.=SUMPRODUCT(--($B$2:$B$10=B2),--($D$2:$D$10=1),$C$2:$C$10)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks