Subtotal in result row returns 0 in excel table

0 votes

I have an excel table ("lstTestSource") like this:

enter image description here

I have a second excel table ("lstTestResult"), which evaluates the first one:

enter image description here

There is a formula in each row in the second column ("lstTestResult"):

=SUMPRODUCT((lstTestSource[Color]=[@Color])*(SUBTOTAL(109; OFFSET(lstTestSource[[#Kopfzeilen];[Amount]]; ROW(lstTestSource[Amount])-ROW(lstTestSource[#Kopfzeilen]); 0))))

The formula is accurate; I can filter by "lstTestSource" and the results will display the correct values in the rows.

A result row appears at the conclusion of "lstTestResult," and no matter what filter is in effect, the result is always 0. There, too, I want to use the SUBTOTAL method.

The automatic calculation is turned on.

I looked into this issue on Google, but I was unable to locate any solutions. Can someone please help me with this?

Sep 24, 2022 in Others by Kithuzzz
• 27,940 points
212 views

1 answer to this question.

0 votes

Syntax

SUBTOTAL(function_num,ref1,[ref2],...)

The SUBTOTAL function syntax has the following arguments:

  • Function_num     Required. The number 1-11 or 101-111 specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

I hope this helps you.
answered Sep 25, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 51,600 points
185 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 51,600 points
200 views
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 51,600 points
515 views
0 votes
1 answer

Export HTML Table to Excel- Doesn't Open in Office 2010

We had the same issue too many ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 51,600 points
147 views
0 votes
1 answer

How to sum column until current row?

Use the Header as your starting point: =SUM(Table1[[#Headers],[Num ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 51,600 points
65 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
523 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
471 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,488 views
0 votes
1 answer

Excel countif entire row in a table

Try this: =COUNTIF(B:B,"my citeria") so if your Column ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 51,600 points
44 views
0 votes
1 answer

Moving Average formula in Excel not autofilling in table

You could use AVERAGEIFS to make the ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 51,600 points
131 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP