SUMIF problem, maybe.

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hi,

I'm not a function person, usually VB, so I'm not sure how to do this or
know if this is possible.
I'll see if I can explain my problem.

I'm trying to create a sum (cell A6) of the average selected columns, with a
checkbox (tick box).

A B C D
1 123 456 789 159
2
3
4 [] [] [] []
5
6 (Average)

When the tick box is ticked this sets the same cell as the tick box to TRUE.
So if the tick box in A4 is ticked then the cell A4 is set to TRUE.
So when the tick box is ticked the value in row 1 is added to the average
total in A6.
For example, if the tick boxes in A4, B4 and D4 where ticked, the value in
A6 would be the average of these three values.

I've tried to use SUMIF, but not sure if this is correct, probably not.
I've tried something like =SUMIF(A4:D4,TRUE, A1:D1/Total selected), didn't
finish the last bit, don't know how.

Hopefully the above text drawing will show the sort of thing I'm looking
for, but if not then please let me know and I will try and explain it a bit
better.

Hope someone can help,
Thanks
Best regards,
Scott
 
Scott,

You were close

=SUMIF(A4:D4,TRUE, A1:D1)/COUNTIF(A4:D4,TRUE)

HTH,
Bernie
Excel MVP
 
Hi Bernie,

Thanks very much, it works a treat, but can I add
something like "" at the end if none of the tick boxes are
selected, like you can in the =IF(SUM(A1:A5)=0,"")),
something like that anyway, which will show a blank cell
if the SUM =0, but in my case if all equal FALSE or no
TRUE's then a blank cell.
Is this possible?

Hope you can,
Thanks
Best regards,
Scott

-----Original Message-----
Scott,

You were close

=SUMIF(A4:D4,TRUE, A1:D1)/COUNTIF(A4:D4,TRUE)

HTH,
Bernie
Excel MVP


Hi,

I'm not a function person, usually VB, so I'm not sure how to do this or
know if this is possible.
I'll see if I can explain my problem.

I'm trying to create a sum (cell A6) of the average selected columns, with a
checkbox (tick box).

A B C D
1 123 456 789 159
2
3
4 [] [] [] []
5
6 (Average)

When the tick box is ticked this sets the same cell as the tick box to TRUE.
So if the tick box in A4 is ticked then the cell A4 is set to TRUE.
So when the tick box is ticked the value in row 1 is added to the average
total in A6.
For example, if the tick boxes in A4, B4 and D4 where ticked, the value in
A6 would be the average of these three values.

I've tried to use SUMIF, but not sure if this is correct, probably not.
I've tried something like =SUMIF(A4:D4,TRUE, A1:D1/Total selected), didn't
finish the last bit, don't know how.

Hopefully the above text drawing will show the sort of thing I'm looking
for, but if not then please let me know and I will try and explain it a bit
better.

Hope someone can help,
Thanks
Best regards,
Scott
Click to expand...


.
Click to expand...
 
Scott,

Of course you can. An easy way is:

=IF(COUNTIF(A4:D4,TRUE)<>0,SUMIF(A4:D4,TRUE, A1:D1)/COUNTIF(A4:D4,TRUE),"")

HTH,
Bernie
Now a former Excel MVP


Scott said:
Hi Bernie,

Thanks very much, it works a treat, but can I add
something like "" at the end if none of the tick boxes are
selected, like you can in the =IF(SUM(A1:A5)=0,"")),
something like that anyway, which will show a blank cell
if the SUM =0, but in my case if all equal FALSE or no
TRUE's then a blank cell.
Is this possible?

Hope you can,
Thanks
Best regards,
Scott

-----Original Message-----
Scott,

You were close

=SUMIF(A4:D4,TRUE, A1:D1)/COUNTIF(A4:D4,TRUE)

HTH,
Bernie
Excel MVP


Hi,

I'm not a function person, usually VB, so I'm not sure how to do this or
know if this is possible.
I'll see if I can explain my problem.

I'm trying to create a sum (cell A6) of the average selected columns, with a
checkbox (tick box).

A B C D
1 123 456 789 159
2
3
4 [] [] [] []
5
6 (Average)

When the tick box is ticked this sets the same cell as the tick box to TRUE.
So if the tick box in A4 is ticked then the cell A4 is set to TRUE.
So when the tick box is ticked the value in row 1 is added to the average
total in A6.
For example, if the tick boxes in A4, B4 and D4 where ticked, the value in
A6 would be the average of these three values.

I've tried to use SUMIF, but not sure if this is correct, probably not.
I've tried something like =SUMIF(A4:D4,TRUE, A1:D1/Total selected), didn't
finish the last bit, don't know how.

Hopefully the above text drawing will show the sort of thing I'm looking
for, but if not then please let me know and I will try and explain it a bit
better.

Hope someone can help,
Thanks
Best regards,
Scott
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top