If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function to Use when compare sums another column
I have and age Field and a Column that contain counts.
If the aging Column contains a number between =0 and =30 I want it to sum up the counts that correspond in field B2. I tried the following and keep getting invalid returns. =SUM(IF(MATCH("=30",Sheet2!F:F,0),INDEX(Sheet2!B: B,MATCH("=30",Sheet2!F:F)),0)) |
#2
|
|||
|
|||
Function to Use when compare sums another column
One way...(I think)...
=sumif(a:a,"="&0,b:b) - sumif(a:a,""&30,b:b) Heather wrote: I have and age Field and a Column that contain counts. If the aging Column contains a number between =0 and =30 I want it to sum up the counts that correspond in field B2. I tried the following and keep getting invalid returns. =SUM(IF(MATCH("=30",Sheet2!F:F,0),INDEX(Sheet2!B: B,MATCH("=30",Sheet2!F:F)),0)) -- Dave Peterson |
#3
|
|||
|
|||
Function to Use when compare sums another column
Hi,
try =SUMPRODUCT(--(Sheet2!F1:F100),--(Sheet2!F1:F10=30)) change range to fit your needs "Heather" wrote: I have and age Field and a Column that contain counts. If the aging Column contains a number between =0 and =30 I want it to sum up the counts that correspond in field B2. I tried the following and keep getting invalid returns. =SUM(IF(MATCH("=30",Sheet2!F:F,0),INDEX(Sheet2!B: B,MATCH("=30",Sheet2!F:F)),0)) |
#4
|
|||
|
|||
Function to Use when compare sums another column
=SUMPRODUCT(--(Sheet2!F1:F1000=0),(--(Sheet2!F1:F1000=30),
Sheet2!B1:B1000) You cannot use full column references with SUMPRODUCT except in Excel 2007+ And if you have Exel2007 then you could use =SUMIFS(Sheet2!B:B,Sheet2!F:F,"=0",Sheet2!F:F,"= 30") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Heather" wrote in message ... I have and age Field and a Column that contain counts. If the aging Column contains a number between =0 and =30 I want it to sum up the counts that correspond in field B2. I tried the following and keep getting invalid returns. =SUM(IF(MATCH("=30",Sheet2!F:F,0),INDEX(Sheet2!B: B,MATCH("=30",Sheet2!F:F)),0)) |
Thread Tools | |
Display Modes | |
|
|