Sum columns by criteria
how to sum range of B:V columns by criteria sum range is B:V
criteria range1 is A:A
criteria1 = "apple"
criteria range2 is row 1
criteria2 = "AN"
the formula sumifs(B:V;A:A;"apple";1:1;"AN") return error? what is wrong?
array formula does not good for me because i have to calculate a lot of fields.
Last edited by rawas; 06-20-2011 at 06:09 AM.
Try
=SUMPRODUCT(($A$2:$A$10="apple")*($B$1:$V$1="AN"),$B$2:$V$10)
adjust ranges to suit and do not use whole column references.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
but i have dinamic range? and array formula has defekt its return error if value in sum range is N/AOriginally Posted by NBVC
If you posted an actual workbook instead of a picture of part of it, and telling us about dynamic ranges in advance... you may get more accurate results the first time. What do you mean by defect?
How are the dynamic ranges defined?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks