Sumproduct question

[mahmed1]

Hi Guys

I have some formula in a1:a7 =IF(H1="","",H1)
and in b1:b7, i have the count of each week

say i have

12 weeks = 2
4 Weeks = 1
8 Weeks = 3
2 Weeks = 2
Blank cell = Blank cell
Blank Cell = Blank cell
Blank Cell = Blank cell

What i want to do is have a formula that will count every thing that is >=8 Weeks
>=3 BUT LESS THAN 8 ETC....

This the formula i have but get a value error which is the part i need to get around (for the left function) (The Blank cells)

=SUMPRODUCT(--($A$1:$A$7<>""),--(LEFT($A$1:$A$7,2)+0>8),$B$1:$B$7)

This is what i get broken down

{1;1;1;1;0;0;0}, {1;0;1;0;#VALUE!;#VALUE!;#VALUE!}, {2;1;3;2;0;0;0}

Its just the value error that i need to get around

 

[Zack Barresse]

Hi there,

Why not just a SUMIF() function?

=SUMIF(B1:B7,">8")

Edit: And your formula probably fails because even though you're trying to parse out the numbers from a text string, it still see's them as text and you'd need to coerce it to a numeric.

 

[mahmed1]

Hi there,

Why not just a SUMIF() function?

=SUMIF(B1:B7,">8")

Edit: And your formula probably fails because even though you're trying to parse out the numbers from a text string, it still see's them as text and you'd need to coerce it to a numeric.

Hi

The reason why i am not using sumif is because in cell B1:B7, I have the count for each based on collumns a1:a7

I dont need to see how many are >=8 in column B as this the count.

The count is already done for each week

i.e

12 Weeks = 2
4 weeks = 1
8 weeks = 3
2 Weeks = 2
Blank Cell = Blank Cell
Blank Cell = Blank Cell
Blank Cell = Blank Cell

So altogether i have 5 that are >=8

The problem i have is that when i convert the left function into numerics based on +0 i get value errors for all blank cells?

=SUMPRODUCT(--($A$1:$A$7<>""),--(LEFT($A$1:$A$7,2)+0>=8),$B$1:$B$7)

 

[Haseeb Avarakkan]

Try;

=SUM(IF(ISNUMBER(LEFT(A1:A7,2)+0),IF(LEFT(A1:A7,2)+0>=8,B1:B7)))

Conformed with CTRL+SHIFT+ENTER, not just ENTER.

 

[barry houdini]

Perhaps try this version

=SUMPRODUCT(--((0&LEFT(A1:A7,2))+0>=8),B1:B7)

That will only cope with up to 2 digits with the weeks, though......