Help with Adding based on criteria

New Contributor

I would like to be able to keep track of our cisco blades cores and what we are using them for.  So I want to be able to put in a blade name, the number of cores, and what it is used for.  I would then like for a formula to be able to look for the X in column C-F and get the number from column B and add that into the total for the cells in J.  Can this be done?

6 Replies

You could have design your workbook in the Excel way. Meaning, you only need 3 columns which are Physical Blade, Cores and Item for example

You going to have something like this
Physical Blade Cores Item
Blade A 24 VMware
Blade C 15 Hyper-V
Blade B 32 Windows
Blade C 21 RHEV

And so one. If your data is in this way,you can easily use PivotTable to summarize your data or use SUMIFS function to achieve the same task
Nevertheless, you can still use PivotTable Wizard of 2007 version to summarize your data with the way you have it.

I wrote a blog that answer this question. Go through it and apply it on your data
Best Response confirmed by kristophersmith (New Contributor)

@kristophersmith Easier would be to put your summary on a row. If you have the latest Excel, you can put the the way you had them in your screenshot with the TRANSPOSE function.


Please see attached.



@Riny_van_Eekelen  How do you get it to total up at the bottom using the number from B based on if there is an X in C or any column..    I really know nothing about formulas and tables within Excel..   What would the function be called?  Is that the sum based on criteria?



The SUMIF function looks for the "x" in the cells above and then sums the values from column B for those rows that have the "x" in either C, D, E or F.




@Riny_van_Eekelen  That worked.  Thanks.