Formula Question

[TMILJVIPM]

Guys I need some help with a formula. Here is what im trying to do:

Cell K1 to subtotal cells F8:F234 but I only want it to sum the cells when I8:I234 have "R" in them

here's what I tried: =subtotal(9,F8:F234),countif(I8:I234,"R")

But this did not work

 

[Aladin Akyurek]

Guys I need some help with a formula. Here is what im trying to do:

Cell K1 to subtotal cells F8:F234 but I only want it to sum the cells when I8:I234 have "R" in them

here's what I tried: =subtotal(9,F8:F234),countif(I8:I234,"R")

But this did not work

Try...

=SUMPRODUCT( SUBTOTAL(9,OFFSET(F8, ROW(F8:F234)-ROW(F8),,1)), --(I8:I234="R"))

 

[marreco]

Good night!

try this...

=sumproduct((I8:234="R")*(F8:234)).

 

[shawnhet]

Try the following:

=SUMPRODUCT(--ISNUMBER(FIND("R",I8:I234)),--
(F8:F234))

Be sure to enter it using Ctrl+Shift+Enter if done correctly { these brackets should appear around your formula}

If you need to sum upper and lower case r - this should work - again use Ctrl+Shift+Enter

=SUMPRODUCT(--ISNUMBER(FIND("R",A1:A3)),--
(B1:B3))+SUMPRODUCT(--ISNUMBER(FIND("r",A1:A3)),--
(B1:B3))

Cheers,

 

[shawnhet]

Oops, ignore the second formula above(it will add columns with both an upper and lower case R twice).

 

[TMILJVIPM]

Try...

=SUMPRODUCT( SUBTOTAL(9,OFFSET(F8, ROW(F8:F234)-ROW(F8),,1)), --(I8:I234="R"))

Yes it worked!! Thanks again

 

[Aladin Akyurek]

Yes it worked!! Thanks again

You are welcome. Thanks for providing feedback.