Offset(?) Formula Challenge 2

[CandyS]

I think this involves an OFFSET function but I'm not certain:

Here's the scenario:

Column A will contain X's (marks items to be taken off list)

Column B contains codes: There are three of these, and there will be many of each in the column: I'll name this range "Range" (how creative!). These codes are:
Metro
NonMetro
Branch

Each code as a value (not listed on the spreadseet, but built into the formula below:
Branch =100
NonMetro =200
Metro =300

Cell C1 contains the following formula that combines the total value of each code:

=(COUNTIF(Range,"Branch&quot*100)+(COUNTIF(Range,"NonMetro&quot*200)+(COUNTIF(Range,"Metro&quot*300)

In Cell C2 I need a formula that basically takes the formula above and conditions it to do the same ONLY for those codes (in column B) that have a corresponding X next to them (in column A).

C3 will have a formula that subtracts C2 from C1. So, cell C1 contains the total of counts of each code multiplied by their respective values; Cell C2 contains the counts of each code marked "X" multiplied by their respective values(and C3 will show the difference between them).

I hope I explained this thoroughly.

Thanks!

Candy

 

[CandyS]

PS. xlbo--thanks again for the first formula (used in the example above)!

Candy

 

[xlbo]

=SUMPRODUCT((range="Branch&quot*(B1:B12="X&quot*(100))+SUMPRODUCT((range="NonMetro&quot*(B1:B12="X&quot*(200))+SUMPRODUCT((range="Metro&quot*(B1:B12="X&quot*(300))

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Zathras]

You can (and probably will) use complicated formulas like you have described (and Geoff is fond of using ), but this is an ideal situation for database formulas.

If you set up a worksheet like this, you will see what I mean. You didn't say how many rows of data you have, but I set up a test worksheet with 24 data rows and one header like this:
[blue]

A1: Select
B1: Code
A2: x
B2: NonMetro
A3..A25   random x's and blanks
B3..B25   random NonMetro, Metro or Branch
C1: =100*I1+200*I4+300*I7
C2: =100*I2+200*I5+300*I8
C3: =C1-C2
F1: Select
G1: Code
F2: x
G2: Branch
F4: Select
G4: Code
F5: x
G5: Metro
F7: Select
G7: Code
F8: x
G8: NonMetro
I1: =DCOUNTA($A$1:$B$25,2,G1:G2)
J1: Total
I2: =DCOUNTA($A$1:$B$25,2,F1:G2)
J2: Selected
I4: =DCOUNTA($A$1:$B$25,2,G4:G5)
J4: Total
I5: =DCOUNTA($A$1:$B$25,2,F4:G5)
J5: Selected
I7: =DCOUNTA($A$1:$B$25,2,G7:G8)
J7: Total
I8: =DCOUNTA($A$1:$B$25,2,F7:G8)
J8: Selected

[/color]
Actually, it's not that hard to set up. The formula in C2 can be copied from C1 and the formulas in I4:J5 and I7:J8 can be copied from I1:J2.

Once you have the individual counts you need in column I, the formulas in C1 and C2 become trivial.

The real value of this approach will be found when you have to make changes, such as adding more codes. By breaking the calculations down into smaller pieces it becomes much easier to debug and maintain.

 

[xlbo]

Z - I ain't fond of 'em as such, I'm just lazy and like to type as little as possible - that's why I started learning VBA. Totally appreciate where you are coming from though - I guess I get used to debugging large formulae and it doesn't bother me that much 'cos if I have problems I just chop 'em up into component parts but I forget that other people may not be as used to these formulae as I....but then again, they may just want the formula for a 1 off bit of work and if so, your approach would take longer to set up in the 1st instance

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[CandyS]

Wow--great stuff, x and zath. I'll give each a shot because I can find other reports to use your recommended solutions.

Thanks again!

Regards,

Candy