Please help! Using SUMIFS but returning 0
HELP. I have been trying to figure out why I am returning 0 with my formula. Please see below:
SUMIFS('Data Detail2'!B:B, 'Data Detail2'!F:F, C10,'Data Detail2'!M:M,"Won",'Data Detail2'!G:G,"2014")
Where B is a column is the record count (this is what I want to sum)
based on:
Column F is Seller Name (referenced by a cell)
Column M is Status of deal (won or lost)
Column G is Year
Please help..I need this tonight..
Your formula looks OK so it must be a data problem.
What results do you get from these formulas:
=COUNTIF('Data Detail2'!F:F,C10)
=COUNTIF('Data Detail2'!M:M,"Won")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If SUMIFS() is returning 0, that suggests to me that there are no records/rows where
column F=the seller name in C10 and
column M=the text string "won" and
column G = the text string "2014"
In order to debug this, you need to double and triple check you data and your criteria ranges to make sure there are matching values. Some specific suggestions of things to look for:
the text string "2014" is not the same as the number 2014. Are you years in column G actual numbers, or are they "numbers stored as text"? Make sure your criteria and criteria range are the same data type.
make sure there are no leading/trailiing spaces or other non-printing characters in your text strings. Excel does not do a "fuzzy" match in this case. It will be looking for exact matches of the text strings, so make sure they match exactly.
Originally Posted by shg
Well, there is a limit to what we can do without seeing your workbook.
Check column B - are the values in there actually text values that look like numbers? Use this formula somewhere:
=SUM('Data Detail2'!B:B)
Does it return a number or zero?
Do you actually have any records which match all three criteria? Is the Seller's Name in C2 spelt the same as how it appears in column F? Are there any leading or trailing spaces?
Does column G actually contain the year, or is it a date and you want to include only those records where the year is 2014?
Hope this helps.
Pete
My thoughts too. I just have no idea where to begin with how my data could be wrong. They both return numbers:
371
2518
See if you get a correct result with this formula:
=SUMIFS('Data Detail2'!B:B,'Data Detail2'!F:F,"*"&C10&"*",'Data Detail2'!M:M,"*Won*",'Data Detail2'!G:G,"2014")
Added wildcards to the criteria.
how can i check the data type? I've made sure they matched exactly when referenced but not sure how to check the underlying properties of the cell..
Can you upload a small samples? Sometime that happens if the data format does not match. So it would be better if we can see your data.
Ah ha! your formula for Column B returned 0. I changed it to 'Number' as a format, but still returns 0. What should it be?
Sadly that did not work, looks like it may be what's in my data?
I checked and all are columns are 'Text' in the Number section on the Home Ribbon
So the numbers in column B are actually text values. Select column B, then click on Data | Text-to-columns and then click on Finish on the first panel. That should convert them to proper numbers.
Hope this helps.
Pete
Deleted by TV
Last edited by Tony Valko; 09-14-2016 at 07:17 PM.
Pete_UK!! That was the problem. wow i never would have figured that out. THANK YOU and THANKS to all!!!!!
Good deal. Thanks for the feedback!
Glad to help - thanks for the rep.
If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks