sumproduct formula work around

  • Thread starter Thread starter Chris
  • Start date Start date
C

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))
 
You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()
 
You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()
Click to expand...

COUNTIF formula works but isn't supported by my other program. Are
there any other workarounds?
 
You don't need to array enter this formula.

In xl2007, there are =sumifs() and =countifs()
Click to expand...

Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?
 
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
 
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
 
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()
Click to expand...

Sorry, meant to say COUNTIFS and SUMIFS aren't supported by my other
program, are there any other workarounds?
 
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?
Click to expand...

It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?
 
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?
Click to expand...

It looks like INDEX is not supported either. Any possible SUMIF or
COUNTIF solutions?
 
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 -
Click to expand...

ok, thanks for your help.
 
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
 
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 -
Click to expand...

ok, thanks for your help.
 
Back
Top