[Solved] Change selection criteria in SUMPRODUCT table

[Pinkmen]

alright so hard to explain with words but what i want to be able to do is use the sumproduct like so.

=SUMPRODUCT($sheet2.$C2:$C10000; 1=MONTH($Sheet2.$A2:$A10000);$Sheet2.$E2:$E10000="A")

That exact formula, and once i drag it to either the right, i would like the 1 on the second array to increase to 2, then 3, so forth. And if i were to
drag it down i would like it to change the A to B, then C.

I have experimented with some simple code, and it works, but when it comes to months, it doesnt.. Anyways, If I have a list of 10 items on the left hand side, and a list of 10 on the top. For example, an expense sheet.

1/24|2/24|3/24|4/24|5/24
dogs
cats
cows
ducks
lizards

How can i make the formula auto add "dogs" and "cats", if the formula is dragged into the corresponding rows. And how do i make the month=1 thing change with its corresponding column?

That was alot of explaining, i hope there is a solution to this. There is no way i can sit on openoffice for 10 hours trying to copy and paste, then change these variables lol. Thx!

Edit: Changed subject, was Draging a complex formula while adding a fixed number  Make your post understandable by others  -- MrProgrammer, forum moderator

[FJCC]

I guess I'm missing something. Can't you place the month values in the first (or any convenient) row and the text values in a column and refer to those cells instead of hard coding the 1 and the "A"? Something like this

Code: Select all

=SUMPRODUCT($sheet2.$C2:$C10000; B$1 = MONTH($Sheet2.$A2:$A10000);$Sheet2.$E2:$E10000= $A2)

[Pinkmen]

Oh my I believe you have solved my problem. So Instead of using "1"=Month(x), Instead using b1=Month(x) allows for the drag to recognize the dragging motion. I shall give it a shot on the bigger project and see if it actually makes filling up alot of cells with respective code 10x easier! Thanks for the different look at this problem!

[FJCC]

Be careful how you write the cells references. If you write B1=Month(x), then when you drag the formula down, it will become B2 = Month(x). You want to always reference the first row, so write B$1 = Month(x). Similarly, you want the E column to always be compared to column A, so write $A2.

[robleyd]

For more information on the use of $ signs, see [Tutorial] Absolute, relative and mixed references

[MrProgrammer]

How can i make the formula auto add "dogs" and "cats", if the formula is dragged into the corresponding rows. And how do i make the month=1 thing change with its corresponding column?

A pivot table creates that layout in a few seconds without the need to write any formulas. You create lots of work for yourself when you ignore this feature of Calc.
Calc Guide Chapter 8 - Using a Pivot Table   (formerly called Data Pilot)

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.