How do I use nested COUNTIF statements inbetween Excel worksheets?
November 3, 2010 4:45 PM   Subscribe

Excel Filter: If, Count and Sum functions, oh my! I need help creating a function that will search between worksheet B, and if certain conditions are met, count the sum of cells in another column (also in worksheet B) and return that result in column A. Mock Excel workbook enclosed!

Column A || Column A Column B
Row 1 || 203 1
Row 2 || 203 2
|| 305 15


What I'm trying to do is have Excel scan all cells in column A and when the values equal a certain value, it will sum it's respective values in column B and display that sum in A1. For example, the function should search column A, and sum 2+1 (because they share the "203" number in column A). Then, the value of "3" should display in Cell A1.

Any help would be greatly appreciated! I've tinkered with COUNTIF, IF, VLOOKUP, SUM and a combination of those functions for over 2 hours with no luck!

Side note: I'm not seeking help for homework. I am a young professional tasked with some Excel worksheets that are a bit over my head. Solving this portion will greatly move things along!
posted by VT@MU to Computers & Internet (9 answers total) 3 users marked this as a favorite
 
Pivot Table! Make a pivot table with Column A as your row label and sum of column B as your value. If you don't know how to do pivot tables look up some online tutorials. It will make your life better.
posted by brainmouse at 4:47 PM on November 3, 2010


Response by poster: Ah brainmouse! Thanks for the quick response, but the nature of this Excel template (and what the resulting data will link to) doesn't allow me to use PivotTables.
posted by VT@MU at 4:49 PM on November 3, 2010


Can't you use SUMIF?

=SUMIF(range,criteria,lookup_range)
posted by keasby at 4:50 PM on November 3, 2010


I am sure I'm missing some subtlety or complexity in your request...but other helpful functions to know are MATCH() and OFFSET()

I have used a handy SUMPRODUCT(IF()) array formula too - if you return 1 or 0 for the if true/fale and multiply that by one of your columns, it gives you the effect of a selective sum product. Send me a private message for more thoughts, or if you want me to play with the sheet.

I love excel. No lie. It's disgusting.
posted by keasby at 4:54 PM on November 3, 2010 [1 favorite]


Best answer: Yeah, as keasby says, you probably want SUMIF. Specifically, in your example, putting something like =SUMIF(B2:B999, 203, A2:A999) into Cell A1 should work. Note that you can put a cell value instead of "203" (e.g.: =SUMIF(B2:B999, C1, A2:A999) will sum up all values in Column B where the corresponding value in Column A is equal to whatever is in Cell C1.
posted by mhum at 5:18 PM on November 3, 2010


Response by poster: Mhum, you got it! Ahhh, I'm very happy to has this resolved. Keasby your answer was still a little over my head b/c I had trouble figuring out which column should be in "range" and which in "look_up". But you both rock at Excel!
posted by VT@MU at 5:27 PM on November 3, 2010


Bonus Pro-tip: Here's a poorly-documented feature of SUMIF. You can actually test for more than just equality. This: =SUMIF(B2:B999, ">=203", A2:A999) will sum up all entries in Column B where the corresponding entry in Column A is greater than or equal to 203. However, if you want to substitute a cell value in your comparison, you have to do this: =SUMIF(B2:B999, ">=" & C1, A2:A999). Weird, huh? Also, I haven't tested this in all versions of Excel so your mileage may vary.
posted by mhum at 5:37 PM on November 3, 2010 [1 favorite]


">=" & C1
is the same as
CONCATENATE(">=", C1)
which just means combine these two things into one expression. Not _that_ weird :)
posted by inigo2 at 5:19 AM on November 4, 2010


which just means combine these two things into one expression. Not _that_ weird :)

Indeed. However, it is a little awkward that: a) the expression has to be a string (e.g.: =IF(A1>=C1,TRUE,FALSE) works but =SUMIF(B:B,>=C1,A:A) does not) and b) Excel can't interpolate references within string expressions (i.e.: there doesn't appear to be any way to make something like =SUMIF(B:B,">=C1",A:A) work).

It took me way too long to finally figure out how to make SUMIF work. Changed my life, it did. It looks like they updated the documentation in Excel 2007 to make it clearer.
posted by mhum at 2:19 PM on November 4, 2010


« Older FB page tagging 101   |   Do you remember this musical toy? Newer »
This thread is closed to new comments.