#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default nested sumif

i need to combine 2 arguments in a SUMIF function so i can use it with Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default nested sumif

How about SUMPRODUCT?

=SUMPRODUCT(--($B$1:$B$50=B50),--($I$1:$I$150=TRUE),($F$1:$F$150))

Hope this helps.
--
John C


"Gluefoot" wrote:

i need to combine 2 arguments in a SUMIF function so i can use it with Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default nested sumif

Try
=SUMPRODUCT(--(B1:B50=B50),--(I1:I50=TRUE),(F1:F50))

"Gluefoot" wrote:

i need to combine 2 arguments in a SUMIF function so i can use it with Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 68
Default nested sumif

How about:
=SUMPRODUCT(--(B1:B50=B50),--I1:I50,F1:F50)

Bob Umlas
Excel MVP

"Sheeloo" wrote in message
...
Try
=SUMPRODUCT(--(B1:B50=B50),--(I1:I50=TRUE),(F1:F50))

"Gluefoot" wrote:

i need to combine 2 arguments in a SUMIF function so i can use it with
Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will
work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to
combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default nested sumif

this worked great!. thanks a lot, i have 1 question though. what does the
"--" in front of the array do exactly?

"John C" wrote:

How about SUMPRODUCT?

=SUMPRODUCT(--($B$1:$B$50=B50),--($I$1:$I$150=TRUE),($F$1:$F$150))

Hope this helps.
--
John C


"Gluefoot" wrote:

i need to combine 2 arguments in a SUMIF function so i can use it with Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default nested sumif

It is 'double unary'. You can do a search on what that means :)
--
John C


"Gluefoot" wrote:

this worked great!. thanks a lot, i have 1 question though. what does the
"--" in front of the array do exactly?

"John C" wrote:

How about SUMPRODUCT?

=SUMPRODUCT(--($B$1:$B$50=B50),--($I$1:$I$150=TRUE),($F$1:$F$150))

Hope this helps.
--
John C


"Gluefoot" wrote:

i need to combine 2 arguments in a SUMIF function so i can use it with Excel
2003. i'm basically trying to make it so a SUMIFS script i wrote will work in
earlier versions of Excel. here are the 2 SUMIF arguments i need to combine...

SUMIF(B1:B50, B50, F1:F50)
SUMIF(I1:I50, TRUE, F1:F50)

so i need to add the cells in F1:F50 where their corresponding value in
I1:I50 is TRUE, and the corresponding value in B1:B50 = B50.

can someone help??

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumif and countif nested? [email protected] Excel Worksheet Functions 3 January 5th 07 05:19 PM
If and nested Sumif error Joco Excel Worksheet Functions 4 July 22nd 06 07:25 PM
Nested SumIf statement jlhart76 Excel Worksheet Functions 7 January 18th 06 05:27 PM
Will a Nested IF/SUMIF function instead of VBA? Ronny Hamida Excel Worksheet Functions 5 January 3rd 06 11:31 PM
nested sumif or sumif with two criteria dshigley Excel Worksheet Functions 5 April 5th 05 03:34 AM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"