Sumproduct corner case not working....

[RzB]

Re the post - "SumIf of SumProducts or summat..."

There seems to be a case where Daniels suggestion don't work
quite right.

If it turns out that if there is only one row in my table
then I get the dreaded #VALUE in columns that are "...".
Hmmm after Peo's first post I took out all the "..."s, then put
them back in after Daniels suggestion seemed to work so well!

These formula are being generated automatically from an
Access (VBA) application. So perhaps I should detect that I
have only one line and do it a different way?

Any thoughts?

Roy

-------------------Daniels Suggestion....

=SUMPRODUCT(A1:A100,B1:B100,--(C1:C100="criteria"))

SUMPRODUCT has the ability to handle text appropriately if you're not
multiply
it explicitly within the formula (same way SUM() does it).


------------------ Original Post
I have a column of numbers and text Col A. The text is just a blanking "---"
for zero values.

I have a second, related column of numbers Col B.

A third related column, ColT contains some text. Thus....

ColA ColB ColT
xx xx ttt
xx xx ttt
xx xx ttt
-------------------
SumX

I want to generate a Sum of the Product of ColA and ColB but only
where ColT matches some defined criteria.

Have spent all afternoon reading and re reading help and books...
Can anyone offer any help...
Many thanks,
Roy

 

[Peo Sjoblom]

Use zeros but with a custom format, i.e.

select A:B and do format>cells>number>custom and use

General;-General;"..."

or if you use regular number formats, do custom and

0.00;-0.00;"..."

that will format display of zeros as ... and still be a numeric format

 

[RzB]

Peo,
Ahh... Yes, now I see....
Many thanks,
Roy