| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using Tables
I have the following formula that works just fine, except that I need help
with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
Sorry...I meant when cell "G" is blank. It ranges from G27+. Thanks.
"jonssmaster" wrote: I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
Try this:
=IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24)
Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
Thank you for the help. I'm a little new at this, so I'm not sure where to
insert the "If" statement into the existing formula and/or what I should be entering in the parenthesis after choose. "T. Valko" wrote: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
The suggestions that have been given to you are changes to your existing
formula. Change =CHOOSE(........) to =IF(G53="","",CHOOSE(........)) Similarly in each other case, just change the relevant sections of your existing formula as Tony suggests. -- David Biddulph "jonssmaster" wrote in message ... Thank you for the help. I'm a little new at this, so I'm not sure where to insert the "If" statement into the existing formula and/or what I should be entering in the parenthesis after choose. "T. Valko" wrote: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
Works Great. Thanks.
One last thing...how would I have N/A show up if G34 is text, like VAC? =IF(G34="","",CHOOSE(SUMPRODUCT(--($A$1:$A$11=E34),--(G34=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(A14:A18=E34),--(G34=B14:B18),D14:D18)+SUMPRODUCT(--(E34=$A$20:$A$24),--(G34$B$20:$B$24),--(G34<=$C$20:$C$24),$D$20:$D$24)+(E50=$A$25)*(G50=$ B$25)*$D$25,"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") "David Biddulph" wrote: The suggestions that have been given to you are changes to your existing formula. Change =CHOOSE(........) to =IF(G53="","",CHOOSE(........)) Similarly in each other case, just change the relevant sections of your existing formula as Tony suggests. -- David Biddulph "jonssmaster" wrote in message ... Thank you for the help. I'm a little new at this, so I'm not sure where to insert the "If" statement into the existing formula and/or what I should be entering in the parenthesis after choose. "T. Valko" wrote: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
IF(ISTEXT(G34),NA(),IF(your existing formula))
-- David Biddulph "jonssmaster" wrote in message ... Works Great. Thanks. One last thing...how would I have N/A show up if G34 is text, like VAC? =IF(G34="","",CHOOSE(SUMPRODUCT(--($A$1:$A$11=E34),--(G34=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(A14:A18=E34),--(G34=B14:B18),D14:D18)+SUMPRODUCT(--(E34=$A$20:$A$24),--(G34$B$20:$B$24),--(G34<=$C$20:$C$24),$D$20:$D$24)+(E50=$A$25)*(G50=$ B$25)*$D$25,"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") "David Biddulph" wrote: The suggestions that have been given to you are changes to your existing formula. Change =CHOOSE(........) to =IF(G53="","",CHOOSE(........)) Similarly in each other case, just change the relevant sections of your existing formula as Tony suggests. -- David Biddulph "jonssmaster" wrote in message ... Thank you for the help. I'm a little new at this, so I'm not sure where to insert the "If" statement into the existing formula and/or what I should be entering in the parenthesis after choose. "T. Valko" wrote: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct using Tables
Thank you VERY much. It works wonderfully.
"David Biddulph" wrote: IF(ISTEXT(G34),NA(),IF(your existing formula)) -- David Biddulph "jonssmaster" wrote in message ... Works Great. Thanks. One last thing...how would I have N/A show up if G34 is text, like VAC? =IF(G34="","",CHOOSE(SUMPRODUCT(--($A$1:$A$11=E34),--(G34=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(A14:A18=E34),--(G34=B14:B18),D14:D18)+SUMPRODUCT(--(E34=$A$20:$A$24),--(G34$B$20:$B$24),--(G34<=$C$20:$C$24),$D$20:$D$24)+(E50=$A$25)*(G50=$ B$25)*$D$25,"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") "David Biddulph" wrote: The suggestions that have been given to you are changes to your existing formula. Change =CHOOSE(........) to =IF(G53="","",CHOOSE(........)) Similarly in each other case, just change the relevant sections of your existing formula as Tony suggests. -- David Biddulph "jonssmaster" wrote in message ... Thank you for the help. I'm a little new at this, so I'm not sure where to insert the "If" statement into the existing formula and/or what I should be entering in the parenthesis after choose. "T. Valko" wrote: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) Or even: +(E53=$A$24)*(G53=$B$24)*$D$24 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(G53="","",CHOOSE(........)) You can save a few keystrokes by replacing that last SUMPRODUCT: +SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24) Can be expressed as: +(AND(E53=$A$24,G53=$B$24)*$D$24) -- Biff Microsoft Excel MVP "jonssmaster" wrote in message ... I have the following formula that works just fine, except that I need help with the formula when G33 is blank. I would like the formula cell (H33) to remain blank if no value has been entered. Any suggestions? =CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53=$A$19:$A$23),--(G53$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does Not Meet","Successful","Excellent","Exceptional","N/A") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Two new tables from one | Excel Discussion (Misc queries) | |||
| Tables | Excel Worksheet Functions | |||
Linear Mode
