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?

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?

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

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?

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?

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?