Calculating values from multiplesheets with multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
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))
 
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".
 
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".
 
I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.
 
I've checked and no extra space is there.
I autofiltered as you suggested and $550.00 is what came up.
 
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?
 
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?
 
Try typing in the value that you are checking for in one of the cells and see
if it works then.
 
Try typing in the value that you are checking for in one of the cells and see
if it works then.
 
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.
 
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.
 
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!
 
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!
 
Back
Top