I need to be able to solve the following problem without arrays or sum product solution. Would be grateful to anyone that can help.
If have a set of data with columns:
1 3 5 2 1 5 2 3
And rows:
Dog
Cat
Fish
Dog
Dog
Cat
and there is data in each cell in this matrix.
I need to sum the values where the column and row values match (i.e. sum all values where column = 1 and row = dog).
Look to employ the sumif function
http://www.techonthenet.com/excel/formulas/sumif.php
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank you for the reply. However, I don't want to do an array - the performance is poor on large data sets. Also, I want to have something that is dynamic. I have one formula that uses sumif, offset and match, but only aggregates multiple row headers.
See attached for an example
Last edited by kimberly01; 10-01-2013 at 12:11 AM.
It might prove helpful to all if you attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
I've posted a sample. Thanks for your help.
Add One more column Feb Total and Sum the Feb Columns with sumif formula
then use the Sumif formula for Dog by using reference of Feb Total Column
Last edited by samba_ravi; 10-01-2013 at 12:48 AM.
I would like to avoid adding a column.
Any other suggestions without inserting additional fields?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks