How to multiply values based on text conditions in Google Sheets

Other Questions Google Sheets
1

I’m working on a Google Sheets formula that needs to multiply numbers based on a text condition in my data. Here’s what I’m trying to do:

  • When the formula encounters the first occurrence of a specific text value, it should use that row’s number as the starting value.
  • For subsequent occurrences of the same text, it should multiply the current number by the previous result.
  • This creates a running multiplication for each text group.

For example, if I have:

  • Row 1: “Apple” with value 2 (result should be 2).
  • Row 2: “Apple” with value 3 (result should be 2*3=6).
  • Row 3: “Apple” with value 4 (result should be 6*4=24).

I managed to create a manual formula for one cell, but I need this to work automatically for the entire column. What’s the best approach to handle this conditional multiplication scenario?

2

You’ll want to combine COUNTIF and OFFSET functions for cumulative multiplication. I’ve done something similar before using an array formula approach. Try =IF(COUNTIF($A$1:A1,A1)=1,B1,B1*INDEX(C:C,ROW()-1)) where column A has your text values, B has numbers, and C is where you put this formula. COUNTIF detects first occurrences and INDEX grabs the previous result. You’ll probably need to tweak the ranges for your data, but this handles the conditional logic automatically when you drag down. Just anchor your ranges with dollar signs so you don’t get reference errors when copying.

3

Try a helper column with conditional multiplication using COUNTIFS. I’ve had good luck with =IF(COUNTIFS($A$1:$A1,$A1)=1,$B1,$B1*C1) where you grab the previous row’s result. The trick is COUNTIFS with an expanding range ($A$1:$A1) automatically spots first instances vs. repeats. First time it sees any text value, it uses the base number. After that, it multiplies by the previous result. Works great across different text groups without hardcoding stuff like “Apple”. Just watch out that your first row doesn’t try to reference a row that doesn’t exist, then it’ll roll down your whole dataset fine.

4

you can also try the SCAN function if you’ve got a newer version of sheets. something like =SCAN(1,B:B,LAMBDA(acc,val,IF(A:A="Apple",acc*val,1))) - though you’ll probably need to tweak it for your setup. works great for running products without getting into complicated indexing.