Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 4th 20, 09:53 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 4
Default Dynamic SUMPRODUCT

I have a spreadsheet with information in columns. The first row contains headers, among these names of months. Today January is in column E.

Columns A to D contains different texts. Some of these are used as criterias

Today I have a fomula like this: =SUMPRODUCT((A2:A100="Criteria 1")*(C2:C100="Criteria 2")*(D2100="Criteria 3")*(E2:E100))

The criteria columns will never change. It will always be A, C and D. The problem is that more columns will be added between D and E, so the column, that contains January will be changed over time. So can this column be made dynamic so it will always point to the column with "January" in row 1?

I have tried using INDEX and MATCH but I can't get it to work.

Jan K.

  #2   Report Post  
Old August 4th 20, 02:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,802
Default Dynamic SUMPRODUCT

Hi Jan,

Am Tue, 4 Aug 2020 01:53:19 -0700 (PDT) schriebst du in
microsoft.public.excel.worksheet.functions:

I have a spreadsheet with information in columns. The first row contains headers, among these names of months. Today January is in column E.

Columns A to D contains different texts. Some of these are used as criterias

Today I have a fomula like this: =SUMPRODUCT((A2:A100="Criteria 1")*(C2:C100="Criteria 2")*(D2100="Criteria 3")*(E2:E100))

The criteria columns will never change. It will always be A, C and D. The problem is that more columns will be added between D and E, so the column, that contains January will be changed over time. So can this column be made dynamic so it will always point to the column with "January" in row 1?


try:

=SUMPRODUCT((A2:A100="criteria 1")*(C2:C100="criteria 2")*(D2100="criteria 3")*INDEX($A$2:$Z$100,,MATCH("January",$A$1:$Z$1,0 )))



Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old August 4th 20, 10:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 4
Default Dynamic SUMPRODUCT

Hi Claus

=SUMPRODUCT((A2:A100="criteria 1")*(C2:C100="criteria 2")*(D2100="criteria 3")*INDEX($A$2:$Z$100,,MATCH("January",$A$1:$Z$1,0 )))

Thank you. Thast did the trick. I don't know what was wrong with my original formula, but it works now.

Jan K.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct and dynamic data jtfalk Excel Worksheet Functions 1 March 7th 10 03:31 AM
Using sumproduct in a dynamic range? [email protected] Excel Discussion (Misc queries) 9 January 1st 08 08:03 PM
Need help please-SUMPRODUCT and Dynamic Range Tasha Excel Worksheet Functions 11 July 18th 07 07:28 PM
Dynamic name reference in SUMPRODUCT MIKWIN Excel Worksheet Functions 5 December 27th 06 08:35 AM
Need help with sumproduct & dynamic ranges bill_s1416 Excel Worksheet Functions 1 March 19th 06 04:36 AM


All times are GMT +1. The time now is 01:53 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017