sumproduct formula work around

[Chris]

Is there any way to replicate this formula with countif or sumif and
not using the array (ctr shift enter) function?

=SUMPRODUCT((A19:A357=A7)*(Y19:Y357=1))

 

[Dave Peterson]

You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()

 

[Chris]

You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()

COUNTIF formula works but isn't supported by my other program. Are
there any other workarounds?

 

[Chris]

You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()

Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?

 

[Mike H]

Hi,

I think you understand that sumproduct doesn't need to be array entered but
the only non array way I can think of without using sumproduct is with a
helper column.

Put this in Z19 and drag down as far as your data in column Y

=A19&Y19

the this formula to add them up.

=COUNTIF(Z19:Z357,$A$7&1)

Me, I'd use sumproduct, why make life hard?

Mike

 

[Elkar]

Could you use a helper column? Say, in Z19 enter the formula:

=IF(AND(A19=$A$7,Y19=1),1,0)

Copy down through through Z357. Then, do a =SUM(Z19:Z357).

HTH
Elkar

 

[T. Valko]

Normally entered:

=SUM(INDEX((A1:A10="x")*(B1:B10=1),,1))

--
Biff
Microsoft Excel MVP

You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()

Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?

 

[Chris]

Normally entered:

=SUM(INDEX((A1:A10="x")*(B1:B10=1),,1))

--
Biff
Microsoft Excel MVP






Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?

It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?

 

[T. Valko]

Any possible SUMIF or COUNTIF solutions?

No

--
Biff
Microsoft Excel MVP

Normally entered:

=SUM(INDEX((A1:A10="x")*(B1:B10=1),,1))

--
Biff
Microsoft Excel MVP






Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?

It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?

 

[Chris]

No

--
Biff
Microsoft Excel MVP







It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?- Hide quoted text -

- Show quoted text -

ok, thanks for your help.

 

[Ashish Mathur]

Hi,

You can use the DCOUNT() function. Assume A18 has "column 1" and Y18 has
"column 2" (w/o quotes)

In cell A359 type, "column 1" and in B359 type "column 2". In A360, type
the text which you have in A7, In B360, type 1. Now in C360, use the
following DCOUNT() formula:

=DCOUNT(A18:Y359,A18,A359:B360)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

You're welcome. Good luck!

--
Biff
Microsoft Excel MVP

No

--
Biff
Microsoft Excel MVP







It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?- Hide quoted text -

- Show quoted text -

ok, thanks for your help.