Excel Excel help-sum if with multi Criteria

C

Clueless Kerry

Joined
Jul 24, 2006
Messages
6
Reaction score
0
I need some help please!
I have an excel that I need to add the numbers in the column if they have certain criteria from 2 other columns.
IE:
Column A- list type (either gadgets, gizmos, whizzer)
Column B- is date (I need to pull mine for each quarter)
Column C-is how many were sold to that customer.

What I want to to is have a formula that will add up column C only if in column A it is a gadget and from column B it was in the month of Jan, Feb, and March.

I did use the sumif formula to have it pull only the gadets: =sumif(A1:A51,"gadget",C1:C51)
How I just need how to figure out how to add date range

Please HELP!!!
Thanks!
 
Ian,
Thanks for the help.
I tried both and still I am unable to have Excel do what I want it do. It came close, but didn't make it.
I tried using the info from both sites.

By the way, I love your "Beaker" picture!

Kerry
 
This is strictly brute force stuff but it will work...

Col A - gadgets, gizmos...
Col B - Date
Column C - numeric values

Column D- =Month(B)
month fxn will extract numeric month value from your dates in Col B

Column E- =IF(AND(A2="gadgets", D2<4),C2,"")

IF-AND combo in column E will filter the numeric values from column C that fits your multiple criteria of gadgets (in Col A) and month less than april (Col D)

Stick a sum of Column E wherever you wish.
 
Klaus,

Thanks for the help!
I haven't tried it yet, but I'm keeping my fingers crossed. I was wondering-do you know how to edit the formula if I need to pull it for let's say-June, July, and August. This would help on a spreadsheet I'm doing for a contest.
Kerry
 
If you wanna do months 6 through 8 then you'd likely go for 5<D2<9. Dont have Excel on this PC so cant be sure. Give the above a try and you may also wanna check examples in excel help on IF-AND function.
 
Last edited:
If you wanna do months 6 through 8 then you'd likely go for 5<D<9
Click to expand...



I used wrong syntax
icon11.gif
. The correct formula for gadgets and months (june, july, august) is



cell D2 =IF(AND(A2="gadgets",D2>5,D2<9,C2,"")



Klaus
 
klaus said:
cell D2 =IF(AND(A2="gadgets",D2>5,D2<9,C2,"")
Click to expand...

Okay, I'm not sure what does the "" at the end of the formula mean or do.
Brute force is not working at this time-I'll have to get tougher with it.

Thanks!
Kerry
 
what does the "" at the end of the formula mean
Click to expand...


Col A - gadgets, gizmos...
Col B - Date
Column C - numeric values

Column D- =Month(B)
month fxn will extract numeric month value from your dates in Col B

Column E- =IF(AND(A2="gadgets", D2<4),C2,"")

IF-AND combo in column E will filter the numeric values from column C that fits your multiple criteria of gadgets (in Col A) and month less than april (Col D)

Stick a sum of Column E wherever you wish.
Click to expand...

It leaves the E2 cell blank if conditions are not met. If you want 0 instead of leaving the cell blank then change the formula to
Column E- =IF(AND(A2="gadgets", D2<4),C2,0)
 
Last edited:
Back
Top