Formula: What am I Doing Wrong

[Paul]

4 A
3 A
2 B
1 A

4 $6.00
3 $6.00
2 $2.00
1 $6.00

In the first example I want to know how many instances of A there are
in column B multiplied by the number in Column A. Answer would be 8.

Formula: sumproduct((a1:a4)*(b1:b4="A"))

But if I want to find out how many instances of $6.00 there are
instead of A, I'm stumped. I get either 0, or 48.

Must be something simple.

 

[Bob Phillips]

It is better to use

=SUMIF(B:B,"A",A:A)

than

sumproduct((a1:a4)*(b1:b4="A"))


I assume you mean

=COUTIF(A:A,6)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul]

Thanks, but I need to count the number of $6.00 in column B times the
number in column A. The answer would still be 8.

Whether I use sumproduct or sumif I still get an answer of 0

4 $6.00
3 $6.00
2 $2.00
1 $6.00

There are 4 $6.00 in the first row, 3 in the second and 1 in the
fourth row for a total of 8. I need a formula to give me that answer.

 

[RagDyeR]

Your problem is probably that you're not using the correct data type in your
formulas.

Exactly what type of data is the "$6.00"?

Is it a *real* XL number?
=Sumif(B1:B4,6,A1:A4)

Is it text?
=Sumif(B1:B4,"$6.00",A1:A4)

Click in one of the cells in Column B, then look at what's displayed in the
formula bar.

Use what you see there in your formula.

If you see a dollar sign in the formula bar, it's text, so enclose it with
quotes (" " ) in the formula.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks, but I need to count the number of $6.00 in column B times the
number in column A. The answer would still be 8.

Whether I use sumproduct or sumif I still get an answer of 0

4 $6.00
3 $6.00
2 $2.00
1 $6.00

There are 4 $6.00 in the first row, 3 in the second and 1 in the
fourth row for a total of 8. I need a formula to give me that answer.

 

[RagDyeR]

Actually, I'm mistaken as to the criteria type.

Sumif() will recognize both types for the criteria.

It's the *totaling* column that needs to be *true* XL numbers in order for
the summing to take place.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Your problem is probably that you're not using the correct data type in your
formulas.

Exactly what type of data is the "$6.00"?

Is it a *real* XL number?
=Sumif(B1:B4,6,A1:A4)

Is it text?
=Sumif(B1:B4,"$6.00",A1:A4)

Click in one of the cells in Column B, then look at what's displayed in the
formula bar.

Use what you see there in your formula.

If you see a dollar sign in the formula bar, it's text, so enclose it with
quotes (" " ) in the formula.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks, but I need to count the number of $6.00 in column B times the
number in column A. The answer would still be 8.

Whether I use sumproduct or sumif I still get an answer of 0

4 $6.00
3 $6.00
2 $2.00
1 $6.00

There are 4 $6.00 in the first row, 3 in the second and 1 in the
fourth row for a total of 8. I need a formula to give me that answer.

 

[Paul]

Must be pilot error. I tried both of your methods on a practice sheet
and they both worked fine. However, in the actual spreadsheet I keep
getting 0 for an answer.

Very frustrating.

 

[Paul]

Figured out my mistake. Was entering $6.00 in stead of $6.25. Duh!

Thanks again for the help with SumIF.

Much appreciated.

 

[RagDyer]

Thanks for your feed-back.

Even posting our mistakes helps to enlighten the archives.