sumproduct formula work around
 

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))

sumproduct formula work around
 

You don't need to array enter this formula.

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



Chris wrote:

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

sumproduct formula work around
 

On Mar 17, 2:47*pm, Dave Peterson wrote:
You don't need to array enter this formula.

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

Chris wrote:

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

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

sumproduct formula work around
 

On Mar 17, 2:47*pm, Dave Peterson wrote:
You don't need to array enter this formula.

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

Chris wrote:

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

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

sumproduct formula work around
 

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

"Chris" wrote:

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))

sumproduct formula work around
 

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


"Chris" wrote:

On Mar 17, 2:47 pm, Dave Peterson wrote:
You don't need to array enter this formula.

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

Chris wrote:

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

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

sumproduct formula work around
 

Normally entered:

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

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
On Mar 17, 2:47 pm, Dave Peterson wrote:
You don't need to array enter this formula.

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

Chris wrote:

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

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

sumproduct formula work around
 

On Mar 17, 3:16*pm, "T. Valko" wrote:
Normally entered:

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

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 2:47 pm, Dave Peterson wrote:

You don't need to array enter this formula.

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

Chris wrote:

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

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?

sumproduct formula work around
 

Any possible SUMIF or COUNTIF solutions?

No

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
On Mar 17, 3:16 pm, "T. Valko" wrote:
Normally entered:

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

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 2:47 pm, Dave Peterson wrote:

You don't need to array enter this formula.

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

Chris wrote:

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

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?

sumproduct formula work around
 

On Mar 17, 3:42*pm, "T. Valko" wrote:
Any possible SUMIF or COUNTIF solutions?

No

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 3:16 pm, "T. Valko" wrote:





Normally entered:

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

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 2:47 pm, Dave Peterson wrote:

You don't need to array enter this formula.

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

Chris wrote:

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

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?- Hide quoted text -

- Show quoted text -

ok, thanks for your help.

sumproduct formula work around
 

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

"Chris" wrote in message
...
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))

sumproduct formula work around
 

You're welcome. Good luck!

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
On Mar 17, 3:42 pm, "T. Valko" wrote:
Any possible SUMIF or COUNTIF solutions?

No

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 3:16 pm, "T. Valko" wrote:





Normally entered:

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

--
Biff
Microsoft Excel MVP

"Chris" wrote in message

...
On Mar 17, 2:47 pm, Dave Peterson wrote:

You don't need to array enter this formula.

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

Chris wrote:

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

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?- Hide quoted text -

- Show quoted text -

ok, thanks for your help.