SUM or SUMIF with 2 criteria & a variable 1

[Skyshadow5]

I need some help please.
I'm using Excel 2003 to return some values from a large spreadsheet. The values need to be summed but only if matching 2 criteria. Column 1 holds a company name (there are 5 different names), column O holds the value. I need the sum of values where column 1 matches 1 of the company names and the value in column O is between 0 & 200. I need to repeat the formula in the adjacent cell but this time with the same company name and the value this time between 201 & 400.
I've tried to include a small sample of the data as an example.
I have seen other answers to multiple criteria questions but they are all text based criteria whereas my second criteria is a value between a specified range which makes it, for me at least, a little more difficult to suss out. Appreciate the help.

Clive

 

[Skyshadow5]

OK so the attachment didn't work.

Comp Value Comp 0-200 201-400 401-600
AAAAA 117.33 AAAAA 200.13 0.00 0.00
BBBBB 84.00 BBBBB 207.40 219.38 0.00
CCCCC 40.00 CCCCC 40.00 208.08 413.17
DDDDD 187.32 DDDDD 382.32 343.36 0.00
EEEEE 263.44 EEEEE 235.45 263.44 0.00
AAAAA 70.80
BBBBB 219.38
CCCCC 413.17
DDDDD 343.36
EEEEE 100.00
AAAAA 12.00
BBBBB 123.40
CCCCC 208.08
DDDDD 195.00
EEEEE 135.45

Hopefully this makes some sense, apologies for not knowing how to use the attachment feature properly.

Clive

 

[SkipVought]

hi,

Actually you have THREE criteria.

I have used Named Ranges based on your heading names.

My result table starts in D1 as such...
[tt]

Comp 0 201 401 601
AAAAA 200.13 0 0
BBBBB 207.4 219.38 0
CCCCC 40 208.08 413.17
DDDDD 382.32 343.36 0
EEEEE 235.45 263.44 0
[/tt]
My formula...
[tt]
E2: =SUMPRODUCT((Comp=$D2)*(Value>=E$1)*(Value<F$1)*(Value))
/tt]
copy down and across. place no formula in the column headed 601.

BTW, I would advise against using a heading like Value, as that name is also a Built-In Function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Skyshadow5]

Thank you Skip, after changing my results table to have the upper and lower criteria values in cells of their own to allow the "less than" and "greater than" signs to work, I named my ranges with their correct titles ("Value" was just a place-holder as was "Comp" to protect the privacy of my data) and that has done the trick; simple when you know how, many thanks my friend.

Clive

 

[SkipVought]

Glad to help!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Skyshadow5]

Another question for you Skip (sorry), how do I count the number of values that make up the total? TIA

Clive

 

[SkipVought]

SUM
[tt]
E2: =SUMPRODUCT((Comp=$D2)*(Value>=E$1)*(Value<F$1)*(Value))
[/tt]

COUNT
[tt]
E2: =SUMPRODUCT((Comp=$D2)*(Value>=E$1)*(Value<F$1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Skyshadow5]

And it was that simple eh, I need a good slap round the head. Mind you it is 01:47 in the morning right now and I'm knackered, thank you again kind sir.

Clive

 

[SkipVought]

And here, across the pond, in North Texas, it is 21:50. Good night!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]