# Malfunctioning =sumif() formula

1. ## Malfunctioning =sumif() formula

I am really stumped at why a sumif formula ALWAYS returns zero.

The basic formula is:

=sumif(\$a\$5:\$a20,"<=\$b\$1",\$b5:\$b25)*\$c\$1

Column A contains sequential dates
Cell B1 has a terminating date
Cells B5:B25 have values of mileage travelled on dates corresponding to column A
Cell C1 has a value at which those mileages should be valued

I have attached a sample Excel spreadsheet showing the layout.

2. ## Re: Malfunctioning =sumif() formula

Is it ok like these:

Excel 2016 (Windows) 32 bit
B
C
34
Total Mileage Cost
35
20.00
\$ 10.40
36
20.00
\$ 19.71

B
C
35
=SUMIF(\$A\$5:\$A25,"<="&\$B\$1,\$B\$5:\$B25)
=SUMIF(\$A\$5:\$A25,"<="&\$B\$1,\$B\$5:\$B25)*\$C\$1
36
=SUMIF(\$A\$5:\$A26,"<="&\$B\$1,\$B\$5:\$B26)
=SUMIF(\$A\$5:\$A26,">="&\$B\$2,\$B\$5:\$B26)*\$C\$2
 Sheet: sumif

``Please Login or Register  to view this content.``

3. ## Re: Malfunctioning =sumif() formula

=SUMIF(\$A\$5:\$A25,"<="&\$B\$1,\$B5:\$B25)*\$C\$1

4. ## Re: Malfunctioning =sumif() formula

Nope! Now, it just flags an error in the formula!

5. ## Re: Malfunctioning =sumif() formula

I used this....
=SUMIF(\$A\$5:\$A25,"<="&\$B\$1,\$B\$5:\$B25)
and it returned 20

6. ## Re: Malfunctioning =sumif() formula

Note that your 2nd formula needs to be...
=SUMIF(\$A\$5:\$A25,">="&\$B\$1,\$B\$5:\$B25)
Ans = 39.40

7. ## Re: Malfunctioning =sumif() formula

Thanks for the help. It worked! Sorry about the delay is responding to thank you.

8. ## Re: Malfunctioning =sumif() formula

It worked. Thanks for the help. Sorry for the delay in replying to give you feedback and toi thank you.

9. ## Re: Malfunctioning =sumif() formula

Happy to help and thanks for the feedback

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