What am I doing wrong with my =sumif ?

[Guest]

I need to sum the data in column A that meets criteria in column B "=J".
I've settled on SUMIF, but it returns zero. Also, now I need to include the
criteria "blank". Am I using the right function? If so, where have I gone
wrong?
A B
1 1.7 J
2 5.3 U
3 5.3 U
4 5.3
5 5.3 U
=SUMIF(B1:B5,J,A1:A5)

 

[Guest]

Try

=SUMIF(B1:B5,"J",A1:A5)

 

[Guest]

=SUMIF(B1:B5,"J,"A1:A5)

J is text so needs be placed in double quotes as shown

If you want to include blank (empty) to could use:

=SUMIF(B1:B5,"J",A1:A5)+SUMIF(B1:B5,"",A1:A5)


HTH

 

[Bill Kuunders]

=SUMIF(B:B,"J",A:A)+SUMIF(B:B,"",A:A)
or B1:B5 and A1:A5

 

[Sandy Mann]

To make your orijnial formula work enclose the *J* in quotes:

=SUMIF(B1:B7,"J",A1:A7)

To add a test for blank cells use:
=SUMPRODUCT(((B1:B7="J")+(B1:B7=""))*A1:A7)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Ragdyer]

Another way:

=SUM(SUMIF(B1:B5,{"J",""},A1:A5))