Sum of Rows based on multiple variables
"I'm not sure that the title quite explains what I am looking for:
I have attached a file with sample data. I am trying to (without using a pivot table) find the number of rows (or sum up the number of stars seen) for each group by month.
The results that I am hoping to see are saved within the file as well (as the second data set). Please help if you can. Thank you. "
Last edited by theinfini; 08-12-2011 at 03:02 PM.
Since you are using 2007, you can use SUMIFS (otherwise you'd probably use SUMPRODUCT). In I2 dragged down
=SUMIFS($D$2:$D$20,$A$2:$A$20,G2,$C$2:$C$20,H2)
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That worked in my trial book, but when I try to recreate the function (using multiple criteria) I can't get it to work. Excel tells me I have input the function incorrectly.
Please advise. (I structure the argument exactly the same, just the Column names change (the letters).
Hard to say without seeing how you restructured it.
1. The first argument is always the range you want summed followed by Critieria 1 range, criteria 1 criteria, etc)
2. The numbers of cells in the ranges need to be consistant.
3. spaces and invisible characters will throw off your matches.
Post the formula you are trying to use. Maybe I can see a formattiing error.
=SUMIF($F$2:$F$30,$A$2:$A$30,H2,$E$2:$E$30,I2)
Error is: You have entered too many arguments.
I caught my error. I was using SUMIF, not SUMIFS
I'm still having an issue and I believe it is because I am using over 150,000 rows. I have heard that this was not supposed to be an issue with 2007. Any suggestions?
SUMIFS should work fine with even a full column (1 million rows).
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks