Formula Help Please

[ccameron]

Hi guys,
I am currently using a formula that has been working well, but I need to add a second crteria and am not sure how to do it. the formula I am currently using is =SUMIF($Q$13:$Q$493,"=2",$R$13:$R$493) I need it to check in column S13:S493 and if the value is "complete" then I dont want it to sum in the total. I only want values that are incomplete to sum up.

Any help would be greatly appreciated,

Thanks guys

 

[T. Valko]

Hi guys,
I am currently using a formula that has been working well, but I need to add a second crteria and am not sure how to do it. the formula I am currently using is =SUMIF($Q$13:$Q$493,"=2",$R$13:$R$493) I need it to check in column S13:S493 and if the value is "complete" then I dont want it to sum in the total. I only want values that are incomplete to sum up.

Any help would be greatly appreciated,

Thanks guys

Try one of these...

This one will work in ALL versions of Excel.

=SUMPRODUCT(--(Q13:Q493=2),--(S13:S493<>"complete"),R13:R493)

This one will work in Excel 2007 and later.

=SUMIFS(R13:R493,Q13:Q493,2,S13:S493,"<>complete")

The SUMIFS version is more efficient if you can use it.

 

[ccameron]

Try one of these...

This one will work in ALL versions of Excel.

=SUMPRODUCT(--(Q13:Q493=2),--(S13:S493<>"complete"),R13:R493)

This one will work in Excel 2007 and later.

=SUMIFS(R13:R493,Q13:Q493,2,S13:S493,"<>complete")

The SUMIFS version is more efficient if you can use it.

Thanks T Valko
I used the second one it worked well, tried the first one =SUMPRODUCT(--(Q13:Q493=2),--(S13:S493<>"complete"),R13:R493) but it only came up with true as an anwser.

Thanks again

 

[T. Valko]

Thanks T Valko
I used the second one it worked well, tried the first one =SUMPRODUCT(--(Q13:Q493=2),--(S13:S493<>"complete"),R13:R493) but it only came up with true as an anwser.

Thanks again

Hmmm...

Can't see how the SUMPRODUCT version will return TRUE.

Glad the SUMIFS worked!

Thanks for the feedback!