Help with Countifs/Sumproduct Formula

Jason Elias

UPDATE: The 10 should be 7. (1*5+1*2)

I have a 2 axis grid. The Cells inside the grid all have letter abbreviations. I'm trying to do subtotals of each row.

How can I make a formula that will sum the products of two rows, when one of the rows has a specific character in the cell?

On Day 1 There are 5x BLUE team that are W (Work) and 2x YELLOW team that are W (WORK) for a total of 7 People working that day. The subtotal column checks every cell in the row. If there is a W, then it will multiply 1 * the QTY value in the top Row.

I'm thinking It involves a combination of SUMPRODUCT and COUNTIFS, but I've been struggling trying to figure this out.

Please help if you can.

Ramzi K

@Jason Elias

Jason, just to make sure I get the calcs correctly, based on what you said in your verbiage, shouldn't the Subtotal for Day 1 be 7 and not 10? ... 1*5 + 1*2

Also why do you need to multiply by 1? That just returns the same number. Or am I misunderstanding how this works.

Cheers,

Ramzi

Jason Elias

@Ramzi K Thank you for the quick response.

Yes. Duh. Should be 7. 1*5 + 1*2.

And Yes. The Quantity in the top row would only every be multipled by 1. I know it seems confusing, but the chart has to be layed out in this way for graphical reasons. Otherwise I'd just put The quantity number in the Cell, but that is not what I want.

Each row represents a work day. Each column represents a group of workers of varying quantity. Not each group works every day, but I still want to know how many people Work on any given day.

Originally I was making a chart where Each worker their own column and I was just using the countifs function to find out how many W's were in each row, but I'm trying to reduce the number of columns.

So on a given day, if the Cell has a W in it, then it will refer to the quantity in the same column.

Does that explain it better. Mine will look more like this:

Ramzi K

@Jason Elias

Try this ... (I kept it simple - you can expand out based on more complexity)

Formula in Subtotal column:

=SUM(COLLECT(RED$1:YELLOW$1, RED@row:YELLOW@row, "W"))

And again since you're multiplying by 1, I excluded that logic to keep it simple.

I hope that helps.

Cheers,

Ramzi