Greetings,
I'm trying to use the sumif function in excel 2010 to find a row in a worksheet then only sum certain columns in the row. Below is sample date and the formula I'm currently using.
Example data:
Column: A B C D....
Row 1: Account#, period 1, period 2, period 3....
Row 2: 105320410, 10, 20, 30...
=SUMIF(A1:D2,105230410,C1:D2)
The result is only the first cell in the range, or 20, instead of the desired result of 50.
Thanks in advance for your help.
Last edited by mennojim; 09-22-2010 at 05:09 PM.
Try perhaps:
=SUMPRODUCT((A1:A2=105230410)*C1:D2)
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.
Thanks for the suggestion. The result is a #VALUE. I'll keep working on a solution, but I attached a sample sheet with the formula it you have time to take a look.
Again, thanks...
Jim
Are you working on one line only?
If so, then:
=IF(A2=105230410,SUM(C1:D2))
in not, then don't include the headers.. with this Sumproduct formula, the summing range should not include any text entries.... so start ranges at A2...
Yes I will have more than one row. So leaving the header our of the range did it. Many thanks...
Re: SUMIF with Horizontal sum range
ATTACHMENT: SumProductTest.xlsx
I am having a bear of a time with this...
I get #VALUE! every time.
Here, I'm trying to get the sum of values in a SINGLE ROW, but for ALL COLUMNS LESS THAN A SPECIFIED VALUE.
Attached is the example, but the formula is
Sample Data:
=SUMPRODUCT(--(A2:A8=A11),--(B1:M1<=B10),B2:M8) Where B10 = 6 A11 = D
12 11 10 9 8 7 6 5 4 3 2 1 TOTAL A 77111 49132 39501 8242 97486 12126 43741 61136 881 67669 89390 48430 594845 B 16684 12374 41735 58793 5214 9668 80971 56374 83355 58720 95534 54072 573492 C 61049 39342 47571 87661 83357 54259 72721 90744 1152 50131 35945 86183 710116 D 40704 55823 74721 66601 99906 27789 82614 65099 61675 40108 56039 10619 681698 E 22726 7758 2705 56655 94641 58430 15497 84563 53900 70989 87005 84132 639001 F 66592 13089 64017 65189 17775 22489 74343 72854 4270 82646 40261 81995 605518 G 10651 51398 80870 45196 33112 66874 19152 20659 27622 27009 93911 86305 562759
Hello gotmike,
welcome to Excel Forum........but please post this as a new thread rather than "hijacking" an old thread - thanks
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks