• 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 or Macros

srinidhi

srinidhi

Active Member
https://docs.google.com/spreadsheet/ccc?key=0ArMlbNwyiUNHdDRjOVJrc1Q5bUZaa25JTWF0SExRNVE


I have posted a sample file in the above mentioned link.

Pls open the file & read the below mentioned line & please do post me your thoughts & solutions.


The figures shld change accoring to the month changed in A1

The figures shld be the sum of the data from Base Data-1 Sheet

C3 should be the sum of USA Region for Plan from April Month

f3 should be the sum of Healthcare for Plan from April Month


The figures shld change according to the Quarter changed in A28

The figures shld be the sum of the data from Base Data-1 Sheet

C3 should be the sum of USA Region for Plan for first quarter

f3 should be the sum of USA Region for Plan for first quarter
 
Hi, srinidhi!


Just as an exercise, yes, it can be done with Sumifs, and yes too, it can be done with macros.


Give a look at the attached files and see if you want to handle formulas like those. If yes, you're done; if not (and I wouldn't unless written by myself), you can try using pivot tables.


Version 97-2003: http://dl.dropbox.com/u/60558749/SumIfs%20or%20Macros%20%28for%20srinidhi%20at%20chandoo.org%29.xls

Version 2007-2010: http://dl.dropbox.com/u/60558749/SumIfs%20or%20Macros%20%28for%20srinidhi%20at%20chandoo.org%29.xlsm


If .xls, check if it works in 2003, I can't do it.


Regards!


PS: By the way, there were two errors in Sheet9, E5:E6 Healthcare duplicated and K3:K4 dev duplicated.
 
Hi ,


Doing the whole worksheet should be on similar lines. C3 can be obtained by the following formula :


=SUMIFS(OFFSET('Base Data-1'!$F$3,0,MATCH(Sheet9!$A$1&Sheet9!C2,'Base Data-1'!$F$1:$AC$1&'Base Data-1'!$F$2:$AC$2,0)-1,ROWS('Base Data-1'!$F$3:$F$24),1),'Base Data-1'!$B$3:$B$24,Sheet9!B3)


entered as an array formula ( using CTRL SHIFT ENTER ).


I have not used the proper relative and absolute addressing , which will have to be done if you want to copy it across and down.


Narayan
 
Hi all,


I have also tried this sumproduct formula :p


http://dl.dropbox.com/u/60644346/srinidhi_workedout.xls


Faseeh
 
Hi, srinidhi!


Keep Faseeh's suggestion, it's easier for further maintenance.


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