CODEWITHSUNDEEP
excelindexingmatchsumifs
Suresh
Suresh

Reputation: 15

Sumif for dynamic range of columns

Hi I have store numbers in different columns and products in rows with sales for respective stores. I want to do sumif in a different sheet by changing stores and products in data validated cells. How do I select dynamic columns in Sumif based on store numbers. Attaching image for reference.

enter image description here

Upvotes: 0

Views: 8233

Answers (2)

tigeravatar
tigeravatar

Reputation: 26670

Here's a mockup based on your sample data, with row numbers and column letters shown:

enter image description here

Using that as a base, in cell C15 is this formula:

=SUMIF($C$3:$C$9,$C$14,INDEX($D$3:$G$9,0,MATCH($C$13,$D$2:$G$2,0)))

You'll need to adjust the ranges to suit your actual data.

Upvotes: 1

0aslam0
0aslam0

Reputation: 1961

=SUMIF(A2:A6,"=Shirts",B2:B6)
  • A2:A6 - This is the range over which the condition applies
  • "=Shirts" - the conditional expression.
  • B2:B6 - The cells under consideration for summing up.

Upvotes: 0

Related Questions