| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embed an array?
I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column
by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
Enter this array formula:
Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
I should have clarified. The formula assumes your array is in rows 1 through
5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
=SUMPRODUCT(--((Jan_G)="G")*((Jan_Inc)-(Jan_Exp))) I tried the above - Jan_G is the "Name" of the range with the "G"'s, i.e. Column A - but it returns an "NA". Thoughts? -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
I used the following formula. The range A1:A5 is named Jan_G. The range
B1:C5 is named Jan_Inc. The range D1:E5 is named Jan_Exp. This formula produces the correct answer: =SUMPRODUCT(--(Jan_G="G")*(Jan_inc-Jan_Exp)) It does not need to be entered as an array formula. Tyro If I name A1:A5 as Jan_G this array formula works fine for me: =SUMPRODUCT(--(Jan_G="G")*((Jan_inc)-(Jan_Exp))) "JanetH" wrote in message ... =SUMPRODUCT(--((Jan_G)="G")*((Jan_Inc)-(Jan_Exp))) I tried the above - Jan_G is the "Name" of the range with the "G"'s, i.e. Column A - but it returns an "NA". Thoughts? -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
Why the double unary minus in that formula? Doesn't the multiplication
already do the job of coercing the boolean TRUE/FALSE to a number? Wouldn't either =SUMPRODUCT((Jan_G="G")*(Jan_inc-Jan_Exp)) or =SUMPRODUCT(--(Jan_G="G"),(Jan_inc-Jan_Exp)) do the job? -- David Biddulph "Tyro" wrote in message t... I used the following formula. The range A1:A5 is named Jan_G. The range B1:C5 is named Jan_Inc. The range D1:E5 is named Jan_Exp. This formula produces the correct answer: =SUMPRODUCT(--(Jan_G="G")*(Jan_inc-Jan_Exp)) It does not need to be entered as an array formula. Tyro If I name A1:A5 as Jan_G this array formula works fine for me: =SUMPRODUCT(--(Jan_G="G")*((Jan_inc)-(Jan_Exp))) "JanetH" wrote in message ... =SUMPRODUCT(--((Jan_G)="G")*((Jan_Inc)-(Jan_Exp))) I tried the above - Jan_G is the "Name" of the range with the "G"'s, i.e. Column A - but it returns an "NA". Thoughts? -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
I got it! My problem was that the arrays for INC and EXP were not the same
size. One was 3 columns and the other was 4 columns. When I made them the same size, it worked. Now I have to figure out how to include that fourth EXP column!! Thanks!!! -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
What is the EXP column?
Tyro "JanetH" wrote in message ... I got it! My problem was that the arrays for INC and EXP were not the same size. One was 3 columns and the other was 4 columns. When I made them the same size, it worked. Now I have to figure out how to include that fourth EXP column!! Thanks!!! -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
the expenses array which has 4 columns.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message et... What is the EXP column? Tyro "JanetH" wrote in message ... I got it! My problem was that the arrays for INC and EXP were not the same size. One was 3 columns and the other was 4 columns. When I made them the same size, it worked. Now I have to figure out how to include that fourth EXP column!! Thanks!!! -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
This will handle that
=SUM(IF(A3:A5="G",Jan_Inc),-IF(A3:A5="G",Jan_Exp)) it is an array formula. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I got it! My problem was that the arrays for INC and EXP were not the same size. One was 3 columns and the other was 4 columns. When I made them the same size, it worked. Now I have to figure out how to include that fourth EXP column!! Thanks!!! -- JanetH "Tyro" wrote: I should have clarified. The formula assumes your array is in rows 1 through 5. Adjust A1:A5 as necessary Tyro "Tyro" wrote in message et... Enter this array formula: Try =SUMPRODUCT(--(A1:A5="G")*((Jan_inc)-(Jan_Exp))) Tyro "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
=SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
Your formula works fine. But must be entered as an array formula
Tyro "Bob Phillips" wrote in message ... =SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
seeing as he was already using an array formula, I guessed he might know
that! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message et... Your formula works fine. But must be entered as an array formula Tyro "Bob Phillips" wrote in message ... =SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
The "obvious" is not always obvious.
Tyro "Bob Phillips" wrote in message ... seeing as he was already using an array formula, I guessed he might know that! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message et... Your formula works fine. But must be entered as an array formula Tyro "Bob Phillips" wrote in message ... =SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Embed an array?
I didn't say it was obvious, I explained the assumption that I made, which I
still say is valid. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... The "obvious" is not always obvious. Tyro "Bob Phillips" wrote in message ... seeing as he was already using an array formula, I guessed he might know that! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message et... Your formula works fine. But must be entered as an array formula Tyro "Bob Phillips" wrote in message ... =SUM(IF(A3:A5="G",Jan_Inc-Jan_Exp)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JanetH" wrote in message ... I have the formula: {=SUM((Jan_Inc),(-Jan_Exp))} where Jan_Inc is a 3 column by X row array representing three types of income numbers, and Jan_Exp is the same for expense numbers. This works fine. Now I want to SUMIF to include only the rows in the array where there is a "G" in the first column of the spreadsheet (not in the array). Is there a way to do this? Thanks. -- JanetH |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EMBED | Excel Discussion (Misc queries) | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| EMBED | Charts and Charting in Excel | |||
| Can I embed swf in excel? | Excel Discussion (Misc queries) | |||
| Embed a US MAP | Charts and Charting in Excel | |||
Hybrid Mode
