Sorry, this was posted to the wrong group originally

I can get this info via pivot table, but there has to be a way to get sum it
via a formula. I have multiple worksheets and each has multiple rows/columns
of data. I want to use 2 different sets of criteria (from different
worksheets) to sum multiple columns of numbers in the master worksheet. I
keep getting #N/A.

{=SUM(IF(($A2=Customer!$F$1:$F$800)*($B2=Sales!$H$ 1:$H$800),Master!L$1:O$800,FALSE))}

Can anyone help?

Try this:

=SUMPRODUCT((Customer!$F$1:$F$800=$A2)*(Sales!$H$1 :$H$800=$B2)*Master!L$1:O$800)

--
Biff
Microsoft Excel MVP


"sk8gfast" wrote in message
...
Sorry, this was posted to the wrong group originally

I can get this info via pivot table, but there has to be a way to get sum
it
via a formula. I have multiple worksheets and each has multiple
rows/columns
of data. I want to use 2 different sets of criteria (from different
worksheets) to sum multiple columns of numbers in the master worksheet. I
keep getting #N/A.

{=SUM(IF(($A2=Customer!$F$1:$F$800)*($B2=Sales!$H$ 1:$H$800),Master!L$1:O$800,FALSE))}

Can anyone help?

I typed it in, but I'm not getting a value back. When I do a visual check,
it should have a number, but it's blank.


"T. Valko" wrote:

Try this:

=SUMPRODUCT((Customer!$F$1:$F$800=$A2)*(Sales!$H$1 :$H$800=$B2)*Master!L$1:O$800)

--
Biff
Microsoft Excel MVP


"sk8gfast" wrote in message
...
Sorry, this was posted to the wrong group originally

I can get this info via pivot table, but there has to be a way to get sum
it
via a formula. I have multiple worksheets and each has multiple
rows/columns
of data. I want to use 2 different sets of criteria (from different
worksheets) to sum multiple columns of numbers in the master worksheet. I
keep getting #N/A.

{=SUM(IF(($A2=Customer!$F$1:$F$800)*($B2=Sales!$H$ 1:$H$800),Master!L$1:O$800,FALSE))}

Can anyone help?

When I input this, I'm not getting a value. It comes back blank. If I hit
F9 to refresh, it still doesn't come up with a correct value. I've done a
visual check and I should have a value in this cell. Help?

"T. Valko" wrote:

Try this:

=SUMPRODUCT((Customer!$F$1:$F$800=$A2)*(Sales!$H$1 :$H$800=$B2)*Master!L$1:O$800)

--
Biff
Microsoft Excel MVP


"sk8gfast" wrote in message
...
Sorry, this was posted to the wrong group originally

I can get this info via pivot table, but there has to be a way to get sum
it
via a formula. I have multiple worksheets and each has multiple
rows/columns
of data. I want to use 2 different sets of criteria (from different
worksheets) to sum multiple columns of numbers in the master worksheet. I
keep getting #N/A.

{=SUM(IF(($A2=Customer!$F$1:$F$800)*($B2=Sales!$H$ 1:$H$800),Master!L$1:O$800,FALSE))}

Can anyone help?

It works for me.

I tried uploading a sample file to a file host but the ones I normally use
seem to be having problems at the moment. Go figure!

--
Biff
Microsoft Excel MVP


"sk8gfast" wrote in message
...
When I input this, I'm not getting a value. It comes back blank. If I
hit
F9 to refresh, it still doesn't come up with a correct value. I've done a
visual check and I should have a value in this cell. Help?

"T. Valko" wrote:

Try this:

=SUMPRODUCT((Customer!$F$1:$F$800=$A2)*(Sales!$H$1 :$H$800=$B2)*Master!L$1:O$800)

--
Biff
Microsoft Excel MVP


"sk8gfast" wrote in message
...
Sorry, this was posted to the wrong group originally

I can get this info via pivot table, but there has to be a way to get
sum
it
via a formula. I have multiple worksheets and each has multiple
rows/columns
of data. I want to use 2 different sets of criteria (from different
worksheets) to sum multiple columns of numbers in the master worksheet.
I
keep getting #N/A.

{=SUM(IF(($A2=Customer!$F$1:$F$800)*($B2=Sales!$H$ 1:$H$800),Master!L$1:O$800,FALSE))}

Can anyone help?