# SUMIF producing unexpected zero results

1. ## SUMIF producing unexpected zero results

Good day

I am hoping someone can help me I am struggling with SUMIF formulas on a specific worksheet. Basically my sheet has a column with cellphone numbers in and another with the duration of the calls. I want to calculate the total call duration of a specific number for the period. So what i have tried is a basic sumif formula but the results stay zero. If I type in the formula as indicated on various websites and this forum i get an error message regarding the apostrophe. If i do a sumproduct formula I get an Value error. The sumif formula i am using is =SUMIF(C:C,"S1",H:H), if i use the same formula without the "" then the answer comes back as zero which is wrong.

Any ideas
Thanks
William

2. ## Re: SUMIF problems

Your formula looks correct assuming column C has the text S1 in it. Hard to be more helpful without being able to see the data...

3. ## Re: SUMIF problems

Hi WilliamRSA,

an attempt

=SUMPRODUCT((C3:C1000=S1)*(H3:H1000+0))

Formula must elaborate in H3:H1000 only time durations: no text strings.

Again, a sample file would be helpful.

Regards

4. ## Re: SUMIF problems

Attached is a sample of my data with two sumif formulas i have tried as well as a sumproduct formula

SUMIf.xlsx

Hope the attachement comes out alright

Regards

5. ## Re: SUMIF producing unexpected zero results

Welcome to the forum.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

6. ## Re: SUMIF producing unexpected zero results

Hi again

(...)

Sorry AliGW...I was slowly writing...

7. ## Re: SUMIF producing unexpected zero results

SUMPRODUCT does not work with entire ranges. replace them with something like B2:B100 ( they must be the same length, though)
Your col B consists of text looking like numbers, not real numbers ( left aligned). To correct, select col C, Data - Text to columns - Finish ( should now be right aligned)

8. ## Re: SUMIF producing unexpected zero results

Originally Posted by Pepe Le Mokko
SUMPRODUCT does not work with entire ranges.
It does actually (I'm assuming you meant columns, not ranges). Not very efficient here though.

9. ## Re: SUMIF producing unexpected zero results

Thanks Pepe

That seems to have fixed it, thank you all for the amazingly quick feedback and help, and a great forum

Kind regards

10. ## Re: SUMIF producing unexpected zero results

Originally Posted by rorya
It does actually (I'm assuming you meant columns, not ranges).
Correct. I wonder where I got that idea...

11. ## Re: SUMIF producing unexpected zero results

Originally Posted by canapone
Hi again

(...)

Sorry AliGW...I was slowly writing...
No need to wait. See the last line of my message.

12. ## Re: SUMIF producing unexpected zero results

Originally Posted by WilliamRSA
Thanks Pepe
Most welcome

13. ## Re: SUMIF producing unexpected zero results

Originally Posted by Pepe Le Mokko
Correct. I wonder where I got that idea...
There used to be an issue with array formulas and entire columns in 2003 and earlier.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1