• 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

tarynmahon

tarynmahon

Member
Hi, I've gone a bit mad and can't seem to get this SUMIFS to work.


Code, Jan, Feb, Mar, Apr, May, Jun

101, 54564, 55, 212, 154, 454, 98

214, 78214, 65, 784, 986, 487, 475

101, 71234, 87, 871, 369, 473, 247


I'm writing my SUMIFS as so;

SUMIFS(B2:G4,A:A,"101",B1:G1,"May")

To get a a result of 927, currently Im just getting a #VALUE! result, please help
 
Thats brilliant thank you, I also need to get a year to date figure, what might you suggest for that please ie. the result to be 128473 ?
 
I'm sure there is abetter way to do it but this works

=SUMPRODUCT((A2:A4=101)*(OFFSET($B$1,,,1,MATCH("May",B1:G1,0))<>"")*(OFFSET($B$2:$B$4,,,,MATCH("May",B1:G1,0))))
 
Hi tarynmahon,


You can also try this:


Code: 
=SUMPRODUCT(($A$2:$A$4=101)*(MATCH(F1,$B$1:$G$1)>=COLUMN($B$1:$G$1)-1),($B$2:$G$4))


...and you need to keep the book opened otherwise formulas will not be calculated.


Regards,
 
This is the actual formula that I have put in as per the SUMPRODUCT suggested above;

A88 = 101

AB86 = May


=SUMPRODUCT(('[Summary budget 2013 v7.xlsx]Switzerland'!$Z$7:$Z$62=$A88)*(MATCH($AB$86,'[Summary budget 2013 v7.xlsx]Switzerland'!$J$3:$U$3)>=COLUMN('[Summary budget 2013 v7.xlsx]Switzerland'!$J$3:$U$3)-1),('[Summary budget 2013 v7.xlsx]Switzerland'!$J$7:$U$62))/1000
 
Hi tarynmahon,


The formula was working fine for me "until" i had workbook opened, it was not changing the results when i closed it. should i upload a sample file?
 
Im not entirely sure what you mean, but I need a formula that works on closed workbooks, is this going to do that? the orginal formula that Hui gave me works fine but you need to have the linked workbook open at the same time which I dont want, if your formula should defintely do what I need then could you upload a sample file please.
 
Im not entirely sure what you mean, but I need a formula that works on closed workbooks, is this going to do that? the orginal formula that Hui gave me works fine but you need to have the linked workbook open at the same time which I dont want, if your formula should defintely do what I need then could you upload a sample file please.
 
You must log in or register to reply here.
Back
Top