Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
I've written the formula below, but I'm getting a result of zero. I'm trying
to sum cells C5:C750 where cells D5:D750 are equal to either a zero or an #N/A value. By just specifying the zero or just specifying the #N/A, I get the correct result, but I need to combine the two. =SUMIF('Business Both Years'!D5:D750,"0"&"#N/A",'Business Both Years'!C5:C750) Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
Use a pair of SUMIF()s
=SUMIF(criteria1) + SUMIF(criteria2) -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: I've written the formula below, but I'm getting a result of zero. I'm trying to sum cells C5:C750 where cells D5:D750 are equal to either a zero or an #N/A value. By just specifying the zero or just specifying the #N/A, I get the correct result, but I need to combine the two. =SUMIF('Business Both Years'!D5:D750,"0"&"#N/A",'Business Both Years'!C5:C750) Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
Personally I would go for the DSUM formula, but without your range and
column headings I can't give you a sensible example. Giff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
Thanks for your prompt response. That works a treat!
In another cell, what would I need to enter to sum cells C5:C750 where D5:D750 DO NOT equal zero or #N/A. When I use the following formula (adopting the solution you suggested), the result identifies a) all the cells in column C where cells in column D do not equal zero and b) all the cells in column C where cells in column D do not equal #N/A, and combining the results, i.e. it's duplicating the results because there is multiple criteria. =SUMIF('Business Both Years'!D5:D750,"<0",'Business Both Years'!C5:C750)+SUMIF('Business Both Years'!D5:D750,"<#N/A",'Business Both Years'!C5:C750) "Gary''s Student" wrote: Use a pair of SUMIF()s =SUMIF(criteria1) + SUMIF(criteria2) -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: I've written the formula below, but I'm getting a result of zero. I'm trying to sum cells C5:C750 where cells D5:D750 are equal to either a zero or an #N/A value. By just specifying the zero or just specifying the #N/A, I get the correct result, but I need to combine the two. =SUMIF('Business Both Years'!D5:D750,"0"&"#N/A",'Business Both Years'!C5:C750) Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
Just subtract our first fomula from the total count of values.
=COUNTA(C5:C750)-the results of the first cell. -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: Thanks for your prompt response. That works a treat! In another cell, what would I need to enter to sum cells C5:C750 where D5:D750 DO NOT equal zero or #N/A. When I use the following formula (adopting the solution you suggested), the result identifies a) all the cells in column C where cells in column D do not equal zero and b) all the cells in column C where cells in column D do not equal #N/A, and combining the results, i.e. it's duplicating the results because there is multiple criteria. =SUMIF('Business Both Years'!D5:D750,"<0",'Business Both Years'!C5:C750)+SUMIF('Business Both Years'!D5:D750,"<#N/A",'Business Both Years'!C5:C750) "Gary''s Student" wrote: Use a pair of SUMIF()s =SUMIF(criteria1) + SUMIF(criteria2) -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: I've written the formula below, but I'm getting a result of zero. I'm trying to sum cells C5:C750 where cells D5:D750 are equal to either a zero or an #N/A value. By just specifying the zero or just specifying the #N/A, I get the correct result, but I need to combine the two. =SUMIF('Business Both Years'!D5:D750,"0"&"#N/A",'Business Both Years'!C5:C750) Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF multiple criteria
That's great! - I used SUM instead of COUNTA, but I still got the desired
result, thank you! "Gary''s Student" wrote: Just subtract our first fomula from the total count of values. =COUNTA(C5:C750)-the results of the first cell. -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: Thanks for your prompt response. That works a treat! In another cell, what would I need to enter to sum cells C5:C750 where D5:D750 DO NOT equal zero or #N/A. When I use the following formula (adopting the solution you suggested), the result identifies a) all the cells in column C where cells in column D do not equal zero and b) all the cells in column C where cells in column D do not equal #N/A, and combining the results, i.e. it's duplicating the results because there is multiple criteria. =SUMIF('Business Both Years'!D5:D750,"<0",'Business Both Years'!C5:C750)+SUMIF('Business Both Years'!D5:D750,"<#N/A",'Business Both Years'!C5:C750) "Gary''s Student" wrote: Use a pair of SUMIF()s =SUMIF(criteria1) + SUMIF(criteria2) -- Gary''s Student - gsnu200747 "Sarah (OGI)" wrote: I've written the formula below, but I'm getting a result of zero. I'm trying to sum cells C5:C750 where cells D5:D750 are equal to either a zero or an #N/A value. By just specifying the zero or just specifying the #N/A, I get the correct result, but I need to combine the two. =SUMIF('Business Both Years'!D5:D750,"0"&"#N/A",'Business Both Years'!C5:C750) Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF and {multiple criteria} | Excel Discussion (Misc queries) | |||
SUMIF WITH MULTIPLE CRITERIA | Excel Discussion (Misc queries) | |||
SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF With Multiple Criteria | Excel Worksheet Functions | |||
SUMIF with multiple criteria | Excel Worksheet Functions |