Hi Guys

I need an array formula which counts the number of instances range Comp1 has
a value greater then zero AND Range Comp2 has a blank value.

Sounds simple but I just can't figure it out.

Also would be helpfull would the same criterior but instead of counting the
instances sums the vales in range Comp1

A B
1 1
3
1 2
1 1
1
4 4

Answer for formula 1 would be 2

and for formula 2 would be 4

Regards
Nelly

Hi,

Q1
=SUMPRODUCT((A1:A6>0)*(B1:B6=""))

Q2
=SUMPRODUCT((A1:A6)*(B1:B6=""))

If you are using named ranges then substitute those for my ranges
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"nelly" wrote:

> Hi Guys
>
> I need an array formula which counts the number of instances range Comp1 has
> a value greater then zero AND Range Comp2 has a blank value.
>
> Sounds simple but I just can't figure it out.
>
> Also would be helpfull would the same criterior but instead of counting the
> instances sums the vales in range Comp1
>
> A B
> 1 1
> 3
> 1 2
> 1 1
> 1
> 4 4
>
> Answer for formula 1 would be 2
>
> and for formula 2 would be 4
>
> Regards
> Nelly

Try
'formula1
=SUMPRODUCT((A1:A10>0)*(B1:B10=""))


'formula 2
=SUMPRODUCT((B1:B10="")*(A1:A10))
OR
=SUMIF(B1:B10,"",A1:A10)


--
Jacob (MVP - Excel)


"nelly" wrote:

> Hi Guys
>
> I need an array formula which counts the number of instances range Comp1 has
> a value greater then zero AND Range Comp2 has a blank value.
>
> Sounds simple but I just can't figure it out.
>
> Also would be helpfull would the same criterior but instead of counting the
> instances sums the vales in range Comp1
>
> A B
> 1 1
> 3
> 1 2
> 1 1
> 1
> 4 4
>
> Answer for formula 1 would be 2
>
> and for formula 2 would be 4
>
> Regards
> Nelly

No need for array formulae, these are normal ones:

=SUMPRODUCT(--(A2:A7>0),--(ISBLANK(B2:B7)))

=SUMPRODUCT(--(A2:A7>0),--(ISBLANK(B2:B7)),A2:A7)

Adjust ranges!

--
Regards!
Stefi



„nellyâ€� ezt Ã*rta:

> Hi Guys
>
> I need an array formula which counts the number of instances range Comp1 has
> a value greater then zero AND Range Comp2 has a blank value.
>
> Sounds simple but I just can't figure it out.
>
> Also would be helpfull would the same criterior but instead of counting the
> instances sums the vales in range Comp1
>
> A B
> 1 1
> 3
> 1 2
> 1 1
> 1
> 4 4
>
> Answer for formula 1 would be 2
>
> and for formula 2 would be 4
>
> Regards
> Nelly

Formula 1
=SUMPRODUCT((A1:A6>0)*ISBLANK(B1:B6))

Formula 2
=SUMPRODUCT((A1:A6>0)*ISBLANK(B1:B6)*(A1:A6))

"nelly" wrote:

> Hi Guys
>
> I need an array formula which counts the number of instances range Comp1 has
> a value greater then zero AND Range Comp2 has a blank value.
>
> Sounds simple but I just can't figure it out.
>
> Also would be helpfull would the same criterior but instead of counting the
> instances sums the vales in range Comp1
>
> A B
> 1 1
> 3
> 1 2
> 1 1
> 1
> 4 4
>
> Answer for formula 1 would be 2
>
> and for formula 2 would be 4
>
> Regards
> Nelly

Thanks for the replies - Many THanks they are all spot on.

Think I was missing the obvious....

"nelly" wrote:

> Hi Guys
>
> I need an array formula which counts the number of instances range Comp1 has
> a value greater then zero AND Range Comp2 has a blank value.
>
> Sounds simple but I just can't figure it out.
>
> Also would be helpfull would the same criterior but instead of counting the
> instances sums the vales in range Comp1
>
> A B
> 1 1
> 3
> 1 2
> 1 1
> 1
> 4 4
>
> Answer for formula 1 would be 2
>
> and for formula 2 would be 4
>
> Regards
> Nelly