INDEX Forumula

[Ruzzo]

Sheet 1 has the following:
A B C D
1 Trucks (20.00) 10.00 10.00
2 Cars (400.00) 20.00 20.00



Sheet 2 has Trucks and Cars listed again as such- how do I INDEX Sheet 2, to input the same info in cells C1 & D1…and C2 & D2, as it has in Sheet 1.

In other words, Sheet 2 may have different values in the ‘B’ column. And the order of Trucks, Cars, and other vehicles may be not in the same order.

Hope this makes sense.

Thanks in advance
Ruzzo

 

[Ruzzo]

Sheet 1

Cars		10		JT
Trucks		8		ER
Cars		5		ER
Boats		7		AV
Cars		11		JT
Boats		2		JT

<colgroup><col style="width: 48pt;" span="5" width="64"> <tbody>
</tbody>

Sheet2

Cars	21
Trucks	0
Boats	2

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B2, just enter and copy down:

=SUMIFS('Sheet 1'!$C$1:$C$6,'Sheet 1'!$A$1:$A$6,A1,'Sheet 1'!$E$1:$E$6,"JT")

Check for stray spaces or non-printable char around the entries in 'Sheet 1'!$E$1:$E$6.

Thank you. I know there's nothing wrong with the formula as it worked in fresh worksheets I created and tried it in.
But, in the worksheet that I have, it seems to have problems with that column E, where the JT, PV and SA are. There are only two character letters in these cells...yet (if H1 has JT) even when I go beside the cell and do a simple =H1, it does not pick up the value JT. I tried formatting column H to 'text' or 'general', or even 'accounting' format, but it just isn't picking up in any of the formulas. I even checked for hidden characters, spaces, unlocked the cell...as you suggested. Even type over it, but it just will not pick up in the formulas. Do you have any other suggestions?
Thanks again...we're there, yet so close.

 

[Aladin Akyurek]

Thank you. I know there's nothing wrong with the formula as it worked in fresh worksheets I created and tried it in.
But, in the worksheet that I have, it seems to have problems with that column E, where the JT, PV and SA are. There are only two character letters in these cells...yet (if H1 has JT) even when I go beside the cell and do a simple =H1, it does not pick up the value JT. I tried formatting column H to 'text' or 'general', or even 'accounting' format, but it just isn't picking up in any of the formulas. I even checked for hidden characters, spaces, unlocked the cell...as you suggested. Even type over it, but it just will not pick up in the formulas. Do you have any other suggestions?
Thanks again...we're there, yet so close.

What do you get on Sheet 1

=LEN(E1)

which you could enter in K1?

Also, enter in L1...

=CODE($E1)

copy across N1. What do we see in K1:N1?

 

[Ruzzo]

What do you get on Sheet 1

=LEN(E1)

which you could enter in K1?


Also, enter in L1...

=CODE($E1)

copy across N1. What do we see in K1:N1?

Thank you for your help once again.
I was actually able to create brand new worksheets and start from fresh...once I put in the formulas that time around, they worked fine. It was odd. Other people have had similar instances with this, where the formula would not pick up a certain cell, but after the re-created the worksheet it's fine. I will try your other solution at some point, but for now, I think I am good to go.
Thanks again.
Ruzzo

 

[Aladin Akyurek]

Thank you for your help once again.
I was actually able to create brand new worksheets and start from fresh...once I put in the formulas that time around, they worked fine. It was odd. Other people have had similar instances with this, where the formula would not pick up a certain cell, but after the re-created the worksheet it's fine. I will try your other solution at some point, but for now, I think I am good to go.
Thanks again.
Ruzzo

You are welcome.

 

[Ruzzo]

2007 and beyond...

=SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,"JT")

I have used this formula with success. What if I want the cell to include both the sum of "JT" and "AV".

Thanks again.

 

[Ruzzo]

2007 and beyond...

=SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,"JT")

I have used this formula with success. What if I want the cell to include both the sum of "JT" and "AV".

Thanks again.

Just to clarify- Cars would have JT on one cell in Column E, and would have AV in another cell. (my example above uses ER). I just want the cell to sum both of those values. If using JT and ER makes it easier for this example, you can use that...the result in that cell should be 26.
Thanks again.

 

[Aladin Akyurek]

2007 and beyond...

=SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,"JT")

I have used this formula with success. What if I want the cell to include both the sum of "JT" and "AV".

Thanks again.

Just to clarify- Cars would have JT on one cell in Column E, and would have AV in another cell. (my example above uses ER). I just want the cell to sum both of those values. If using JT and ER makes it easier for this example, you can use that...the result in that cell should be 26.
Thanks again.

=SUM(SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,{"JT","AV"}))

If you would like to have JT and AV in a range of their own like B1:C1 for example:

=SUMPRODUCT(SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,B1:C1))