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.

✔ Best Answer

My test sheet does Notdo that, even on a completely empty sheet.How are you modifying the formulas?

What else is going?

MIKE

The following formula totals amounts in column PNo 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

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.

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 arange of cells

in columns X / Q / R?

MIKE

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.

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 incolumn Q OR R (or neither)as they are options for the item sold in column XSo 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 dataThe only column that MUST have data is column X

If column X = "a" then total column P

Correct?

MIKE

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.

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

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?

My test sheet does Notdo that, even on a completely empty sheet.How are you modifying the formulas?

What else is going?

MIKE

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. :-)

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History