• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs on Rows

A

aks_npti

New Member
Hi,


I have a range of data which is in below format :


Cities February March April May

London 4 6 0 9

Paris 3 3 2 4

New York 2 2 4 4

Tokyo 1 2 4 3

London 7 4 5 1

Paris 3 0 2 3

New Delhi 7 4 2 1

Manila 5 6 7 2

Tokyo 3 4 1 1


I want to represent the data in below format : Total represent the summation of values across the months for the respective city.


Cities Total

London

Paris

New York

Tokyo

New Delhi

Manila


I tried SUMIFS with Match & Index with no luck. I don't want an helper column with summation of values.


Any help will be higlhy appreciated.


Regards,

Aks
 
This should be what you are after. I showed two ways of getting what you want, hopefully lol. Personally, pivot tables are a lot easier.


https://www.dropbox.com/s/hmy1lhii227cqho/Sumifs%20vs%20Pivot%20Table.xlsx
 
Good day aks_npti


Nick M has pointed the way to a brighter future, Tables and Pivot Tables, with a Pivot Table you can slice and dice your data as you want.
 
Hi Nick & Bobhc,


Thanks for your replies and insight. I should have mention in the query itself that I dont want to use pivot table method . Sorry about this!!!


The way Nick represented in the formula version I was also able to achieve that however my desired output format is :


Cities Total

London

Paris

New York

Tokyo

New Delhi

Manila


I think we need a formula consisting of Sumifs & Index + Match to achieve this...
 
Hi aks_npti


See if this helps. The data has been arranged in the range A1:E10.


For unique list of cities in column F: =IF(COUNTIF(A2:A10,A2)=1,A2,"")


For sum for cities across months: =SUM(IFERROR(SEARCH(F4,$A$2:$A$10),0)*($B$2:$E$10)). Press Ctrl+Shift+Enter to use array formula for calculating sum.


Cheers

Amritansh
 
Hello Aks,


If I understand your requirement correctly, then you are looking for the total value for each city across the months (FEB to MAY).


Say your unique list of cities are from A13 to A18 as follows (and initial / parent data spread across A1 to E10):


London

Paris

New York

Tokyo

New Delhi

Manila


Now at B13, write:


=SUMPRODUCT(($A$2:$A$10=$A13)*($B$2:$E$10)) press enter and drag it down..


Let us know if this fine.


Kaushik
 
You must log in or register to reply here.
Back
Top