help with formula=)

[sidel80]

Hi all,

Just a quick question. Is there a function that will allow me to do this:

Example:

10 20 30 40 50

MktA 1 3 6 2 5
MktB 3 8 1 4 7
MktC 4 7 8 9 0


It will link to another spreadsheet which will basically look at Market A and also look at the top row (I.E. 10, 20, 30, 40, 50) and pull from the right columns. That would mean I would have to specify that in Market A, Tier 1, I am looking at only the numbers in the 10’s, 40’s, and 50’s column.

Tier 1 Tier 2
A
B
C
D

Sure, I can just link them, but if the columns change, it would screw up my data. Any function intuitive enough to look at the column heading and column row? Thanks in advance.!

[Zack Barresse]

you could use the INDEX/MATCH to bring back a single specified cell. but i'm having trouble following what your trying to accomplish and the established rules by which your sheet is abiding (that you need set). care to post with the HTML maker?

[sidel80]

Sorry, feel kinda stupid, but how do I post a screenshot?[/url]

[Zack Barresse]

download Colo's cool HTML Maker (linnk is at bottom of page), install it as an add-in.

if you need further help, post back.

edit: btw, to see how to install, posted earlier today. http://www.mrexcel.com/board2/viewtopic.php?t=83039

[sidel80]

Ok, hope this works. Basically I'm trying to find a function that will sum all the numbers in the data table that fall into the market and Tier level I've specified. In this example, cell B16 would have a value of 12. Market A - Tier level 1: 19.99 = 7 and 25.99 = 5.

I hope that's a little more clear. Thanks!

[******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data Table
2		19.99	25.99	30.99	49.99	250	1000
3	Market A	7	5	3	5	4	6
4	Market B	4	4	2	4	7	9
5	Market C	1	6	2	3	5	3
6
7
8
9
10
11
12
13
14										Tier Value
15		Tier 1	Tier 2	Tier 3					Tier 1	Tier 2	Tier 3
16	Market A	12							19.99	30.99	49.99
17	Market B								25.99	250
18	Market C									1000
19
20
21
Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.][/code]

[Brian from Maui]

Not sure but here's a couple of options with assumptions,


******** ******************** ************************************************************************>

Microsoft Excel - Book2 ___Running: 11.0 : OS = Windows XP
File Edit View Insert Options Tools Data Window Help About
B9C9 =
	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	*	19.99	25.99	30.99	49.99	250	1000
3	A	7	5	3	5	4	6
4	B	4	4	2	4	7	9
5	C	1	6	2	3	5	3
6	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	*	12	12	*	*	*	*
10	*	19.99	25.99	*	*	*	*
Sheet1 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The SUM formula is arrary entered, but not the SUMPRODUCT. I'd prefer the SUMPRODUCT.

[sidel80]

Sorry, I'm a bit confused. I tried plugging in the same formula (=SUMPRODUCT(IF(B2:F2<=C2,B3:F3))) but for some reason, its adding up all the numbers in Row A.

However, I think I see the reasoning behind the formula. But it won't be able to add up Tier 2, 30.99 and 1000. Does that make sense? Thanks for the help, I will try to see if I can make this work

[Zack Barresse]

no, no. your mixing his formulas together - not a good idea.

i'm kinda/sorta following your example, still pretty difficult to follow. what determines what Tier the information falls into? are they pre-assigned? and what data are you wanting to bring back, exactly? don't forget to include the qualifier's for your required data.

[sidel80]

Sorry, the Tier table is basically determined by me. I specify that in Tier 2, it would include 30.99, 250, and 1000. Is it confusing that I'm using numbers as a heading? I changed it to reflect animals instead

So what I'm trying to do is, sum up all the numbers that fall into their respective Tiers. In the example I gave

Market A
-cell B16 would have a value of 12 (B3+C3)
-cell C16 would have a value of 13 (D3+F3+G3)
-cell D16 would have a value of 5 (E3)

Here I just linked it, but I was hoping for a formula that will look at my data table and reference it to my Tier Table and poof, I get a sum. Sorry if my explanation is ALOT confusing


[******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B16C16D16 =
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data Table
2		dogs	cats	fish	insects	horses	birds
3	Market A	7	5	3	5	4	6
4	Market B	4	4	2	4	7	9
5	Market C	1	6	2	3	5	3
6
7
8
9
10
11
12
13
14										Tier Value
15		Tier 1	Tier 2	Tier 3					Tier 1	Tier 2	Tier 3
16	Market A	12	13	5					dogs	fish	insects
17	Market B								cats	horses
18	Market C									birds
19
Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.][/code]

[Zack Barresse]

okay, this may get confusing, i'm at home w/ a non-functional HTML Maker...

i copied your information straight from this post (hopefully that helps). i can post this in a few hours when i'm at work if you'd like.

to start, i moved your tier information to start in A7.
A7:A9 houses Tier 1, Tier 2, Tier 3, respectively.
from B7:G9 houses your criteria. here's the hard part, and the backbone of my (somewhat convuluted) solution. you see how you have 'dogs' in B2? well in B7:B9 is the only location where dogs will go. i did this with data validation, so i could only pick that. each column of the new 'Tier qualifiers' must house their own 'data specific' types of labels (text).

as an example, Tier 3. B9, C9, D9 . . . F9 G9: all blank. E9 houses 'insects'.
this is what i mean by 'data specific'.

now, in B16, i typed the following:
=SUMPRODUCT(--($B$2:$G$2=$B$7:$G$7),$B3:$G3)
C16:
=SUMPRODUCT(--($B$2:$G$2=$B$8:$G$8),$B3:$G3)
and D16:
=SUMPRODUCT(--($B$2:$G$2=$B$9:$G$9),$B3:$G3)

select B16:D18, press Ctrl + D (autofill)

i know this is jumbled, and there's probably a better solution (but i'm tired, sorry). i'll look at it again tomorrow, when i'm awake, unless someone posts a better solution.

if you need explanations on any, don't hesitate to ask.