Solved Formula for two variables

Microsoft Office 2007 home and student
June 3, 2011 at 08:22:15
Specs: Windows Vista
The following formula totals amounts in column P if an "a" is found in column X and also in either column Q or column R. (Thanks to my previously answered question).

=SUMPRODUCT(--(X1:X60="a"),((Q1:Q60="a")+(R1:R60="a")),(P1:P60))

If I just wanted a formula to total the amount in column P if an "a" is in column X and column Q, how would I adjust it? I don't think I fully understand how SUMPRODUCT is working because when I try to make the adjustment to the formula I come up with an error message.


See More: Formula for two variables

Report •

✔ Best Answer
June 6, 2011 at 13:10:24
My test sheet does Not do that, even on a completely empty sheet.

How are you modifying the formulas?

What else is going?

MIKE

http://www.skeptic.com/



#1
June 3, 2011 at 12:27:38
The following formula totals amounts in column P

No it does not....

Your original request was:

If there is an "a" in column X, and EITHER an "a" in column Q OR column R, then I want it to show the amount in column P.

All you wanted was to SHOW the amount in column P, you did not want to total the column.

The formula I provided will SHOW you the amount in column P, it will NOT TOTAL column P.

Do you want to simply SHOW or do you want to TOTAL a range of cells that are in column P?

MIKE

http://www.skeptic.com/


Report •

#2
June 3, 2011 at 12:50:02
My apologies. I thought the range in the formula made it do that. I should never have assumed.

Ideally, I would like it to total the amounts in column P if those conditions exist in the other columns. My table (excluding header row) begins in row 8 and continues until row 110.

I have decided it probably best to separate the Q & R information. So, I ultimately will need two different formula's. One that totals every amount in column P if there is an "a" in column X and column Q. Then another formula that totals amounts in column P if there is an "a" in column X and column R. I can then add the two totals afterwards if needed.


Report •

#3
June 3, 2011 at 13:05:27
I'm still not clear on how your data is set up.

Does the "A" appear in only one specific cell,
or can it be located within a range of cells
in columns X / Q / R?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 3, 2011 at 13:18:51
Each row represents a sale. The "a" shows as a checkmark (cell formatted as Webdings). So there could be a's in more than one row. In each row an "a" could possibly be in column X as it specifies a certain type of sale, but it will only ever be in column Q OR R (or neither) as they are options for the item sold in column X, never an "a" in both Q and R in the same row.

I hope that helps a bit more...sorry for my lack of clarification.


Report •

#5
June 3, 2011 at 13:57:26
In each row an "a" could possibly be in column X as it specifies a certain type of sale, but it will only ever be in column Q OR R (or neither) as they are options for the item sold in column X

So you only really need to be concerned with column X.

Since column Q / R may have data
and
Since column Q / R may not have data

The only column that MUST have data is column X

If column X = "a" then total column P

Correct?

MIKE

http://www.skeptic.com/


Report •

#6
June 6, 2011 at 07:40:39
No. Maybe this will help...

Formula 1: For every row that there is an "a" in column X and in column Q, I want the corresponding amounts shown in column P to be added up. If there is no "a" in column X or Q, then the amount in column P should not be included in the total.

Formula 2: For every row that there is an "a" in column X and in column R, I want the corresponding amounts shown in column P to be added up. If there is no "a" in column X or R, then the amount in column P should not be included in the total.


Report •

#7
June 6, 2011 at 12:05:45
OK, I get it now.

Let me see what I can do.

MIKE

http://www.skeptic.com/


Report •

#8
June 6, 2011 at 12:15:16
In your original message, my warning:
The only caveat is you can have only ONE occurrence of the target letters,
Only one row at a time, else your answer will be incorrect.

Was given under the impression you only wanted to SHOW the totals,
the formula will actually do what you want by totaling the rows.

If you want them split apart, so Q totals by itself and R totals by itself then
try this:

=SUMPRODUCT(--(X2:X61="a"),--(Q2:Q61="a"),(P2:P61))

=SUMPRODUCT(--(X2:X61="a"),--(R2:R61="a"),(P2:P61))

MIKE

http://www.skeptic.com/


Report •

#9
June 6, 2011 at 12:20:19
That looks to work except for one thing. If there is nothing found it says false. Is there a way for it to show $0.00 instead of FALSE?

Report •

#10
June 6, 2011 at 13:10:24
✔ Best Answer
My test sheet does Not do that, even on a completely empty sheet.

How are you modifying the formulas?

What else is going?

MIKE

http://www.skeptic.com/


Report •

#11
June 6, 2011 at 13:16:08
My apologies. Upon checking again, it looks like I copied the formula over properly to one cell but not the other. In short, I ended up with two formulas in the cell.

Your formula above works great.

Thank you so much for your help solving my problem and your patience getting there. :-)


Report •

#12
June 6, 2011 at 13:25:41
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Ask Question