Special COUNTIF

[petiteshiraz]

I want to add up all the values in column R (R:R) but I only want to add the portion of the values that are equal to or under $10,000.

So, if a cell has a value of $70,000 – I only want to count $10,000 of it. If the value is $6,000, then I want to count the whole amount.

How do I write this?

 

[Fazza]

maybe using COUNTIF for <10000 and multiply by 10000?

 

[petiteshiraz]

That doesn't seem correct.

 

[Fazza]

you're right

I was too hasty

what about adding up all values and then subtracting, along the lines of my earlier post, those >=10000 ?

I'll start up Excel, check it & get back to confirm

 

[barry houdini]

Try using an array formula like this

=SUM(IF(R2:R100<10000,R2:R100,10000))

confirmed with CTRL+SHIFT+ENTER

 

[Fazza]

what about

=SUMIF(R:R,"<10000",R:R)+COUNTIF(R:R,">=10000")*10000

 

[petiteshiraz]

=SUMIF(R:R,"<10000",R:R)+COUNTIF(R:R,">=10000")*10000


I think that might be close but it gives me values above $10,000 not equal to or less than $10,000.

 

[petiteshiraz]

Scratch that... I think that did work.

Thanks.

 

[Fazza]

best to double check, I might be wrong. though close - so a simple fix should be available

I'm ducking out for a little while, so won't be online for the next hour, btw