Calculating values from multiplesheets with multiple criteria

[Roger Govier]

Hi

Compared with the sample data you posted, there are too many "Z" s in
your criteria.
Also, it looks as though your code numbers are numeric and don't need
the " " around

=SUMPRODUCT(--(I2:I44="Operating"),--(K2:K44="ZLEBLD110"),--(J2:J44=21005),(L2:L44))

 

[Guest]

So close....
It returned a result of $0.00
It should have shown $550.00
What now

 

[Guest]

So close....
It returned a result of $0.00
It should have shown $550.00
What now

 

[Guest]

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

 

[Guest]

Try something for me. Use autofilter to select all the rows that match the
conditions you want and see if you get $550. I'm wondering if the value in
the account # is something like " 12345" instead of "12345".

 

[Guest]

Hi Roger,
The extra "Z" is from "fat fingers", It's one in the morning after all.

 

[Guest]

Hi Roger,
The extra "Z" is from "fat fingers", It's one in the morning after all.

 

[Guest]

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

 

[Guest]

I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.

 

[Roger Govier]

Hi

I get a result of 550 with the data as posted.
Copy my formula and paste it to your sheet rather typing.

If it still doesn't work, it is your data.
I had to retype your Amounts as they came through as text values from
the posting, but other than that the data was as you sent.

In a spare column type =L2+0 and copy down.
Does it give the values as in column L?

 

[Roger Govier]

Hi

I get a result of 550 with the data as posted.
Copy my formula and paste it to your sheet rather typing.

If it still doesn't work, it is your data.
I had to retype your Amounts as they came through as text values from
the posting, but other than that the data was as you sent.

In a spare column type =L2+0 and copy down.
Does it give the values as in column L?

 

[Guest]

Try typing in the value that you are checking for in one of the cells and see
if it works then.

 

[Guest]

Try typing in the value that you are checking for in one of the cells and see
if it works then.

 

[Guest]

Success.
I must have been missing or adding something in the formula.

Thanx for that

 

[Guest]

Success.
I must have been missing or adding something in the formula.

Thanx for that

 

[Guest]

Success. I think I have it working.
Now for the next one, can i produce that sumproduct calculation into a
different sheet? In short, can i combine the sumproduct calcultion from two
seperate sheets Say 05-06 and 06-07 into Sheet 3?

Thankyou for your patience.

 

[Guest]

Success. I think I have it working.
Now for the next one, can i produce that sumproduct calculation into a
different sheet? In short, can i combine the sumproduct calcultion from two
seperate sheets Say 05-06 and 06-07 into Sheet 3?

Thankyou for your patience.

 

[Guest]

You'll need to replace F2:F20 (for example) with something like this:
Sheet2!F2:F20. Just make sure that each range is the same length.

Good luck!

 

[Guest]

You'll need to replace F2:F20 (for example) with something like this:
Sheet2!F2:F20. Just make sure that each range is the same length.

Good luck!