Function or Macro ?

[David OShea]

Hi Guys
I am using Excel 2000 and Win2000 Pro. I don't know if this should be done in a formula or
macro.
Basically I need to check the values of three cells on each row. If each cells value comes
within range
of the specified value then I add the C cell value to the sum value in C8.

So in the example below:
C1:C3 are added because A1:C1, A2:C2 and A3:C3 all match the criteria. Where as rows
A4:C4, A5:C5 & A6:C6 do not,
So C4:C6 are not added to the Total Sum in C8.

A B C
Equal to 99 Starts with 401 Equal or < 100
-------------------------------------------------------------
1 | 99 401222 100
2 | 99 401333 100
3 | 99 401555 99
4 | 99 456789 100
5 | 200 432145 300
6 | 200 401666 100
7 | ---------
8 Sum = 299

I hope I have explaned this properly. Any help would be appricated.

Rgds
David O'Shea
gmail @ oshead

 

[Mark Lincoln]

Hello David,

Assuming your figures in column B are numbers, this formula works for
me in C8:

=SUMPRODUCT(--(A1:A6=99),--(INT((B1:B6)/1000)=401),C1:C6)

 

[Mark Lincoln]

P.S. Actually, the formula also works if the figures in column B are
formatted as text. I just tried it.

 

[David OShea]

Mark
Thank you v.much for a speedy reply. This works perfectly.
Your a gentleman.
Rgds
David O'Shea

 

[RagDyer]

Actually, the criteria for Column C is *not* addressed !

Mark's formula does not check if Column C is <=100.

If you *do need* that additional stipulation to be met, try this:

=SUMPRODUCT((A1:A6=99)*(--LEFT(B1:B6,3)=401)*(C1:C6<=100)*C1:C6)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Mark Lincoln]

David,

RagDyer is correct; I missed the last criterion. (Thanks, RD, for
catching that!)

My corrected formula (a bit different from RagDyer's) reads:

=SUMPRODUCT(--(A1:A6=99),--(INT((B1:B6)/1000)=401),--(C1:C6<=100)*C1:C6)

Sorry for the mistake.

 

[sali]

what about idea not to use "sumproduct", but to have conditional formula in
column next to "c" which would be either equal to "c" or 0, depending of
criteria.
now, all you have to do is copy that formula into each row, and finaly make
sum(above) at the bottom.
this is easier since you have not to adjust row index in each element of
sumproduct formula, if resizing array.

formula for d1 could be:
=if(a1=99 and left(str(b1);3)="401" and c1<=100;c1;0)
when copiing formula a1 becomes a2 and so on ...
[formula is not sintax checked, just shown as idea]

 

[Mark Lincoln]

Sali,

That's certainly a valid method - I use it myself when I want to
quickly eyeball which cells match whatever criteria I'm setting. But
David wanted a formula in C8 which would work with the existing values.

If you add rows, the ranges in the formula will adjust themselves
accordingly.

 

[David OShea]

Thats great guys. All help is highly appricated.

Rgds
David