Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 4th 05, 09:49 PM
dshigley
 
Posts: n/a
Default nested sumif or sumif with two criteria

Say for example I have three columns; the first is a name column, second is
color and third is cost. I am trying to create a formula that will sum all
cells in the third column (cost) that matches the criteria of the name "Bob"
in column A AND "Red" in column B. I know I am close and I know I can use
the "*" somehow but I can't figure out the syntax. Any help is appreciated.

  #2   Report Post  
Old April 4th 05, 10:01 PM
Duke Carey
 
Posts: n/a
Default

Try


=sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)


"dshigley" wrote:

Say for example I have three columns; the first is a name column, second is
color and third is cost. I am trying to create a formula that will sum all
cells in the third column (cost) that matches the criteria of the name "Bob"
in column A AND "Red" in column B. I know I am close and I know I can use
the "*" somehow but I can't figure out the syntax. Any help is appreciated.

  #3   Report Post  
Old April 4th 05, 10:11 PM
dshigley
 
Posts: n/a
Default

I did a search on my question before I posted and one of last years answers
was the exact same thing. I tried that formula but cannot seem to get it to
work. Thanks though.

Dan

"Duke Carey" wrote:

Try


=sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)


"dshigley" wrote:

Say for example I have three columns; the first is a name column, second is
color and third is cost. I am trying to create a formula that will sum all
cells in the third column (cost) that matches the criteria of the name "Bob"
in column A AND "Red" in column B. I know I am close and I know I can use
the "*" somehow but I can't figure out the syntax. Any help is appreciated.

  #4   Report Post  
Old April 4th 05, 10:33 PM
Peter Aitken
 
Posts: n/a
Default

"dshigley" wrote in message
...
Say for example I have three columns; the first is a name column, second
is
color and third is cost. I am trying to create a formula that will sum
all
cells in the third column (cost) that matches the criteria of the name
"Bob"
in column A AND "Red" in column B. I know I am close and I know I can use
the "*" somehow but I can't figure out the syntax. Any help is
appreciated.


In my experience, if you want to sum based on 2 or more criteria you have to
use DSUM. SUMIF works with only 1 criterion.


--
Peter Aitken

Remove the crap from my email address before using.


  #5   Report Post  
Old April 4th 05, 11:19 PM
bj
 
Posts: n/a
Default

Duke's equationm should have worked
put in a temporary column and use
=if(a2="Bob",1,2)
and copy down to row 100
See3 if all the "Bob" come up
to check if you might have extra spaces or something.
do likewise with "Red"

"dshigley" wrote:

I did a search on my question before I posted and one of last years answers
was the exact same thing. I tried that formula but cannot seem to get it to
work. Thanks though.

Dan

"Duke Carey" wrote:

Try


=sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)


"dshigley" wrote:

Say for example I have three columns; the first is a name column, second is
color and third is cost. I am trying to create a formula that will sum all
cells in the third column (cost) that matches the criteria of the name "Bob"
in column A AND "Red" in column B. I know I am close and I know I can use
the "*" somehow but I can't figure out the syntax. Any help is appreciated.



  #6   Report Post  
Old April 5th 05, 03:34 AM
Harlan Grove
 
Posts: n/a
Default

"dshigley" wrote...
I did a search on my question before I posted and one of last years answers
was the exact same thing. I tried that formula but cannot seem to get it

to
work. Thanks though.

....

In what way does the formula not work? It does work if you've given correct
specs and have ensured your data range contains no leading or trailing
spaces in the entries in columns A and B and all entries are numbers rather
than text looking like numbers in column C.

"Duke Carey" wrote:
Try

=sumproduct(--(A2:A100="Bob"),--(B2:B100="Red"),C2:C100)

....

Basic formula debugging. What do the formulas

=COUNTIF(A2:A100,"Bob")
=SUMPRODUCT(--(TRIM(A2:A100)="Bob"))
=SUMPRODUCT(--(TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob"))

=COUNTIF(B2:B100,"Red")
=SUMPRODUCT(--(TRIM(B2:B100)="Red"))
=SUMPRODUCT(--(TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red"))

=SUM(C2:C100)
=SUMPRODUCT(--C2:C100)

return? If the first 3 and/or second 3 return different results, you have
either normal ASCII or nonbreaking HTML spaces in your columns A and/or B.
If the last two formulas return different results, some or all of your
column C values are text. The most robust formula would be

=SUMPRODUCT(--(TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob"),
--(TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red"),--C2:C100)

However, the best approach would be to clean up the data in A2:C100.




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
How do I use the sumif if I have multiple criteria (i.e. greater . Kellyatisl Excel Worksheet Functions 3 June 4th 06 06:33 AM
SUMIF using VLOOKUP as criteria Telly Excel Worksheet Functions 1 February 18th 05 10:17 PM
"criteria" in a sumif refering to the value in another cell mark Excel Discussion (Misc queries) 1 January 31st 05 07:39 PM
How do I ask for multiple criteria when creating a "sumif" formul. Rachelle Excel Worksheet Functions 3 December 1st 04 11:49 PM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 07:52 PM


All times are GMT +1. The time now is 12:18 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017