sum if question

sum if question

Post by Graha » Fri, 11 Jun 2004 12:44:40


I have a summary page on a sheet that i want to use to
count entries on a details page.

I want to use the sum if ( i think )- scenario is
column a date , column b person , column c method.

I would like to have a summary that reads
Sum if - column B = Graham , Column C = email , Column C
= some time in last week.

Equall I would like to do the same to capture same datat
but for anything prior to last week.

Hope i make sense !!!
 
 
 

sum if question

Post by Julie » Fri, 11 Jun 2004 14:23:40

Hi Graham

you'll actually need the sumproduct function

for last week
=SUMPRODUCT((A2:A8="Graham")*(B2:B8="Email")*(C2:C8>=NOW()-7))

for previous weeks
=SUMPRODUCT((A2:A8="Graham")*(B2:B8="Email")*(C2:C8<NOW()-7))

Cheers
JulieD

 
 
 

sum if question

Post by Bob Philli » Fri, 11 Jun 2004 16:08:04

Be careful using NOW, it includes the hh:mm:ss as well. You might be needing
TODAY()

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
 
 

sum if question

Post by carl » Fri, 18 Jun 2004 21:38:54

My data looks like so:

ColA ColB
abc441 10
def232 20
abc221 10
XYZ220 30

I would like to create a sumif that will look at the left
3 characters in ColA, compare to the below table , and if
there is a match, sum.

abc
def

Thank you in advance.
 
 
 

sum if question

Post by Domeni » Fri, 18 Jun 2004 21:47:25

Hi Carl,

Using your table, and assuming that Cell A7 contains abc and Cell A8
contains def, and so on, try putting this formula in B7 and copy down:

=SUMIF($A$1:$A$4,"abc*",$B$1:$B$4)

Hope this helps!

In article <1dba101c45468$0d28b4c0$ XXXX@XXXXX.COM >,
 
 
 

sum if question

Post by Peo Sjoblo » Fri, 18 Jun 2004 21:57:42

Assume you put abc in E1 and the example table in A1:B4


=SUMPRODUCT(--(LEFT($A$1:$A$4,3)=E1),$B$1:$B$4)

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
 
 

sum if question

Post by Andy » Fri, 18 Jun 2004 22:05:16

Hi

How about something like:
=SUMPRODUCT((LEFT(A2:A5,3)={"abc","def"})*(B2:B5))

--
Andy.
 
 
 

sum if question

Post by QW5u » Sat, 30 Jun 2007 04:06:11

column A will say activation fee and col b will
have either 9.99 or 29.99 so if auth/9.99 then i want it to sum those and if
auth/29.99 then i want it to sum those separately.
 
 
 

sum if question

Post by VG9wcGVyc » Sat, 30 Jun 2007 04:28:09

=SUMIF(B:B,9.99,A:A)
 
 
 

sum if question

Post by QW5u » Sat, 30 Jun 2007 04:36:07

this is what i want:
here's
the entire spreadsheet layout

col A col B
Authfee 9.99
tranfee 9.99
authfee 9.99
authfee 29.99

so i want the totals to be
9.99 auth fees, 2 =19.98
29.99 auth fees, 1 = 29.99
 
 
 

sum if question

Post by TWlrZSB » Sat, 30 Jun 2007 04:50:03

I think I have it

I can't do it in 1 cell because it's an array but try:

=SUMPRODUCT((A1:A20="Authfee")*(B1:B20=9.99)*(B1:B20))

Enter with control + shift + enter
You can change Authfee and yhr 9.99 for other values
but remember to re-enter with Ctrl+Shift+Enter

to get the count of items dive the answer this gives by 9.99.

Mike
 
 
 

sum if question

Post by VG9wcGVyc » Sat, 30 Jun 2007 04:50:04

=SUMIF(A:A,"Authfee",B:B)

=SUMIF(A:A,"tranfee",B:B)
 
 
 

sum if question

Post by VG9wcGVyc » Sat, 30 Jun 2007 04:52:03

sorry ... should read your question more carefully ..

=SUMPRODUCT(--(A2:A100="Authfee"),--(B2:B100=9.99))

=SUMPRODUCT(--(A2:A100="Authfee"),--(B2:B100=29.99))
 
 
 

sum if question

Post by VG9wcGVyc » Sat, 30 Jun 2007 05:00:03

SUMPRODUCT is entered with Enter not Ctrl+Shift+Enter

=SUMPRODUCT(--(A1:A20="Authfee"),--(B1:B20=9.99)) will give count