Home > Back-end >  Count number of items in each row, multiply by their value and then sum over rows, all in one formul
Count number of items in each row, multiply by their value and then sum over rows, all in one formul

Time:07-16

I have a table of the following type:

Price Item 1 Item 2 Item 3 Item 4
50 2 2 1 1
75 1 2 2 2

What I'd like to do in Google Sheets is to (a) sum the number of items with a certain ID in each row, (b) multiply the number with the price in each row, and then (c) sum over the results in each row. So for ID 1, the formula should give 2 x 50 1 x 75 = 175, and for ID 2 it should give 2 x 50 3 x 75 = 325.

This is straight forward if one breaks this up in two steps by adding additional columns, but I have so many item columns and item IDs that this is infeasible. I looked at ways to use COUNTIF row-wise using arrayformula, but couldn't get it to work in conjunction with the multiplication in each row.

Is there a way to do steps (a-c) in a single formula in Google sheets?

CodePudding user response:

Use enter image description here

  • Related