LinkBack URL
About LinkBacks
I have a sheet in which the months are given in rows and there is another sheet in which I have to give the sum of certain products in the months columns which are aligned horizontally. Attached sheet for reference.
I have a sheet in which the months are given in rows and there is another sheet in which I have to give the sum of certain products in the months columns which are aligned horizontally. Attached sheet for reference.
Hi,
You can use SUMPRODUCT:
For your uploaded sample, formula in E3, copied down and across to G9:
Formula:
=SUMPRODUCT(('Fruits Summary'!$B$3:$B$14=$D3)*('Fruits Summary'!$D$3:$D$14=E$2)*'Fruits Summary'!$C$3:$C$14)
See attached.
Or with the more efficient SUMIFS
=SUMIFS('Fruits Summary'!$C:$C,'Fruits Summary'!$B:$B,$D3,'Fruits Summary'!$D:$D,E$2)
Sumifs and Sumproduct is much efficient for numbers and here you wish a result as number so above two solutions are really good but if there is text result then I must say Index and Match combination would be good.
An alternate solution try
E3Formula:=IFERROR(INDEX('Fruits Summary'!$C$3:$C$14,MATCH(E$2&$D3,INDEX('Fruits Summary'!$D$3:$D$14&'Fruits Summary'!$B$3:$B$14,),0)),"")
Copy across
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Instead of selecting complete column range, select range as per actual data range.
Formula:=SUMIFS('Fruits Summary'!$C$3:$C$14,'Fruits Summary'!$B$3:$B$14,'Combined List'!$D3,'Fruits Summary'!$D$3:$D$14,'Combined List'!E$2)
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
That is assuming that new data is not being added, otherwise the formula would need to be changed. Restricting the range is not necessary with SUMIFS unless slow performance is noticed, in which case dynamic named ranges would be the method of choice.
For text results, this array formula would be more efficient than the array concatenation suggested in post 4
=IFERROR(LOOKUP(2,1/IF('Fruits Summary'!$B$3:$B$14=$D3,'Fruits Summary'!$D$3:$D$14=E$2),'Fruits Summary'!$C$3:$C$14),"")
Array confirmed with Shift Ctrl Enter
best used with dynamic named ranges if more data is likely to be added at a leter point.
Jason Lookup function can handle array and there would be no need for CSE. Just modify as below. If you go with If condition it will definitely require CSE.
Formula:=IFERROR(LOOKUP(2,1/(('Fruits Summary'!$B$3:$B$14=$D3)*('Fruits Summary'!$D$3:$D$14=E$2)),'Fruits Summary'!$C$3:$C$14),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks