| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Will sumproduct work? | Excel Worksheet Functions | |||
| SUMPRODUCT formula doesn't work! | Excel Discussion (Misc queries) | |||
| I have never been able to get a SumProduct formula to work..Help! | Excel Worksheet Functions | |||
| sumproduct doesn't work | Excel Worksheet Functions | |||
| Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) | |||
Linear Mode
