• 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 Function Help

O

ODee

New Member
Hello - I'm trying to get the aggregate amount of $$ in a column that meet two criteria. I am trying to get a snapshot of this data from one worksheet which I will call COM and collating all the data on a second worksheet. However when I reference the COM worksheet I am getting a VALUE error. When I use the SUMIFS function directly on the COM worksheet however the formula works. I seem only to be having the problem on the 2nd worksheet when I populate the formula with ’COM’! What am I doing wrong? Can anyone help?

The formula I have used is
=SUMIFS('COM'!O2:O100, 'COM'!O2:O100,"Signed",’COM’!H2:H100,">12/31/2016")
 
Hi ,

I cannot be sure , but have you copied and pasted the formula , or have you typed it in , in your post ?

How many worksheets are you referencing in your formula ? Is it only one worksheet named COM or are you referencing two different worksheets , one named COM , and the other named ’COM’ ?

If it is the latter , then since the two characters surrounding the text COM are the left single quotation mark and the right single quotation mark , which are special characters , the entire worksheet tab name will have to be enclosed within single quote marks ; your revised formula will then be :

=SUMIFS('COM'!O2:O100, 'COM'!O2:O100,"Signed",'’COM’'!H2:H100,">12/31/2016")

where the newly introduced single quote marks are highlighted in red.

If it is the former , just remove the two characters surrounding the text COM ; your revised formula will then be :

=SUMIFS('COM'!O2:O100, 'COM'!O2:O100,"Signed",COM!H2:H100,">12/31/2016")

Surrounding the worksheet tab name with single quotes is not necessary unless the name includes spaces or special characters.

Narayan
 
Hi Narayan,

Sorry I typed it in.
I'm only referencing one worksheet. I removed the quotes as there are no special characters and Im still getting the value error. Please help?
 
Check Column O, it can't act as text value criteria range and sum range !

=SUMIFS('COM'!O2:O100,'COM'!O2:O100,"Signed",’COM’!H2:H100,">12/31/2016")

Regards
Bosco
 
Last edited:
bosco_yip said:
Check Column O, it can't act as text value criteria range and sum range

=SUMIFS('COM'!O2:O100,'COM'!O2:O100,"Signed",’COM’!H2:H100,">12/31/2016")

Regards
Bosco
Click to expand...

Thanks Bosco. That was a typo. I really should have copied and pasted what I actually used but avoided due to abundance of caution and typing causes problems. What I actually typed, having removed the quotes since there are no special characters, did contain the relevant column containing the Signed criteria range. What could be wrong?

=SUMIFS(COM!O2:O100,COM!G2:G100,"Signed",COM!H2:H100,">12/31/2016")
 
Do you want to sum or actually Count the items matching the criteria?
=COUNTIFS(COM!O2:O100,"Signed",COM!H2:H100,">12/31/2016")
 
=SUMIFS(COM!O2:O100, COM!G2:G100, "Signed", COM!H2:H100, ">"&Date(2016,12,31))
 
You must log in or register to reply here.
Back
Top