Sumif based on two variable
 

i am trying to get the sum of data from column S that meets criteria from
column b and d-

I have been able to get a count using the following formula:
=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6))

following a few examples I found I tried the following but i keep getting
#Value

=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

what am I doing wrong?

Sumif based on two variable
 

Do you have any #VALUE! errors in any of the referenced ranges?

Try it like this:

=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr."),--('Sheet2'!$B$6:$B$609='Sheet1'!$C6),'Sheet2'!$S$6: $S$609)

--
Biff
Microsoft Excel MVP


"Memphus01" wrote in message
...
i am trying to get the sum of data from column S that meets criteria from
column b and d-

I have been able to get a count using the following formula:
=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6))

following a few examples I found I tried the following but i keep getting
#Value

=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

what am I doing wrong?

Sumif based on two variable
 

The double minus is unnecessary:
=SUMPRODUCT(('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2' !$B$6:$B$609='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

If you get #value error, that probably means your data in col S contains
#VALUE! error(s). Clean it up, and it should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Memphus01" wrote:
i am trying to get the sum of data from column S that meets criteria from
column b and d-

I have been able to get a count using the following formula:
=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6))

following a few examples I found I tried the following but i keep getting
#Value

=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

what am I doing wrong?

Sumif based on two variable
 

Thanks- I went back and I did have some bad data- frustrated me to no end,
but at least I learned something :)

"Max" wrote:

The double minus is unnecessary:
=SUMPRODUCT(('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2' !$B$6:$B$609='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

If you get #value error, that probably means your data in col S contains
#VALUE! error(s). Clean it up, and it should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Memphus01" wrote:
i am trying to get the sum of data from column S that meets criteria from
column b and d-

I have been able to get a count using the following formula:
=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6))

following a few examples I found I tried the following but i keep getting
#Value

=SUMPRODUCT(--('Sheet2'!$D$6:$D$609="Sr.")*('Sheet2'!$B$6:$B$609 ='Sheet1'!$C6),'Sheet2'!$S$6:$S$609)

what am I doing wrong?

Sumif based on two variable
 

Welcome. Please take a moment to press the YES button (like the one below) in
ALL responses which helped to answer your query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Memphus01" wrote:
Thanks- I went back and I did have some bad data- frustrated me to no end,
but at least I learned something :)