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.  Register To Reply

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   Register To Reply

3. Re: Malfunctioning =sumif() formula

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

4. Re: Malfunctioning =sumif() formula

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

5. Re: Malfunctioning =sumif() formula

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

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  Register To Reply

7. Re: Malfunctioning =sumif() formula

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

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.  Register To Reply

9. Re: Malfunctioning =sumif() formula

Happy to help and thanks for the feedback   Register To Reply

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