problem sumproduct ?

Hi

Not sure exactly what you are trying to achieve but SUMPRODUCT requires all ranges to be the same size.

Why not post a sample and a description of what you want to achieve?

Ed

hi Daddylonglegs,

Thanks for your formula, but it does not work , return #value
F40:AJ40 have Text only (eg : AB or AC), Text and number (eg : A2 or A8), and Blank

Thanks for your help again

No, if you have entries like "AC" that formula won't work, presumably if you have "AC" on a non-Sunday it should count as zero?

It's difficult to do with SUMPRODUCT because you need to coerce the RIGHT function to numeric...but if you do that where the cell contains text you get an error.

Why not stick with SUM(IF....?

You could perhaps use

=SUM(IF((WEEKDAY($F$7:$AJ$7)<>1),IF(ISNUMBER( SUBSTITUTE(F40:AJ40,"A",0)+0),SUBSTITUTE(F40:AJ40,"A",0)+0)))

confirmed with CTRL+SHIFT+ENTER

Dear both,

Thanks for your help
according to me, my formula as below

=SUMPRODUCT(--(NOT(TEXT($F$7:$AJ$7,"ddd")="SUN"))*(LEFT((F40:AJ40),1)="A"),ISNUMBER(--RIGHT(F40:AJ40))+0)

It works, but it returns as countif function, i wanna return sumifs function

how to fix it ?

Thanks agian

As I said in my last post....

That's difficult because you can't easily derive the number from an entry like "A3" without getting an error because you are also trying to derive a number from "AC".

A formula like Mike's would work using SUMPRODUCT but as it also needs CSE I presume that would negate the advantage of SUMPRODUCT for you?

What is the extent of the values in F40:AJ40, you say you can have "AB", "AC", "A2" and "A8", how many other combinations are there? If there are a limited number then you could probably use a non-CSE formula to do what you want.

Ultimately the best advice for you, I think, would be to consider redesigning your spreadsheet. Clearly the setup you have means using complex formulas that are harder to debug and maintain. A simpler setup, perhaps having the numbers in a separate range to the letters, would allow you to employ simpler, more easily adaptable formulas.

Thanks for you advice, i successfully used the sum function combine ctrl - shift enter already, but i wanna find the second formula

Thank you agian