• 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.

Excel 2003 SUMIF Function

P

Pitcher

Member
Hey guys,


Hoping someone can help me with this sumif function I have been TRYING to apply =SUMIF('CEM Q3 2011-Q2 2012'!A3:U6766,B3,'CEM Q3 2011-Q2 2012'!Q3:Q6766)


The function returns a 0 although within CEM Q3 2011-Q2 2012'!Q3:Q6766 there are values. It appears that the formula is not picking up the values within this range located on a seperate worksheet. I want to be in a position to drag the formula down which I know requires $ in certain locations but before I get to that stage I want to understand why the value is not being returned. Could be anything to do with formatting?


Does anyone have any insight as to what the issue may be? I have completed sumif functions before across multiple sheets without issue. I can send on workbook if that helps?


Many thanks,

Andrew
 
are you sure your range of Q3:Q6766 are all numbers but not partially "Text" or something else?
 
Hi, Pitcher!


SUMIF arguments:

1st, testing range, A3:U6766

2nd, criteria, B3

3rd, sum range, Q3:Q6766


Is that right? Because criteria and sum range are part of testing range. It won't work.


Regards!
 
@fred

Hi!

Years don't come alone... :)

I use two pairs...

Regards!

PS:... of sunglasses, of course!
 
The $ signs don't matter in this case


The fact that the Criteria and Sum Ranges intersect the Source Range also doesn,t matter, although I am sure it isn't what Pitcher wants, it is allowable


I'd suggest that the formula should be Array entered with Ctrl+Shift+Enter as the Source Range is a 2D range and the Criteria Range is not a single cell or criteria
 
Thanks Guys,


Very strnage that it won't work. There is a cell range in there that covers the entire data within the CEM worksheet called 'cem' and have tried this formula =SUMIF(cem,"Trevor Healy",'CEM Q3 2011-Q2 2012'!Q3:Q6766) with no luck.


No idea!
 
Hi Pitcher,


Your 1st Post:

=SUMIF('CEM Q3 2011-Q2 2012'!A3:U6766,B3,'CEM Q3 2011-Q2 2012'!Q3:Q6766)


In place of U please use A.. as SumIf Range only checks single Column.


Your 2nd Post:

I dont know the range for "cem" Name, but It also should be A:A or B:B not A1:B100, I mean Testing Range's Column should be Single..


Your 3rd Post: (coming soon..)

If you really want to check all the cell from A1 to U6766, where there is a Value of B3 found, and regardless number of B3.Value found more than one's..

Just add the corresponding Q row (regardless Circular Reference)..

Ninja's are taking lunch... wait for some time.. :)


Regards,

Deb
 
Might have been covered before, but might also check for trailing spaces in either the testing range or criteria cell. I see this a lot with data that has been imported from somewhere else.
 
Hi, Pitcher!


I agree with Hui's allowance, when I wrote that it wouldn't work I tried to mean that in my opinion it wasn't what you required.


Try Debraj Roy's suggestion of replacing U by A and check if you get your expected results (you can shorten to 10 rows or so to manual check it).


And if tested range is actually A:U, test criteria is B (within tested range), please consider uploading a sample file. See second green sticky post at this forums main page for guidelines.


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