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 !!!

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

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

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)

TODAY()

--

HTH

Bob Phillips

... looking out across Poole Harbour to the Purbecks

(remove nothere from the email address if mailing direct)

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.

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.

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 >,

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 >,

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

=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

Hi

How about something like:

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

--

Andy.

How about something like:

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

--

Andy.

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.

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.

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

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

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

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

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

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

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

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

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

SUMPRODUCT is entered with Enter not Ctrl+Shift+Enter

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

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

1. New IFS interface (jfs.ifs, udf.ifs for example)

2. Sumproduct - multiple Sum ifs

6. COUNTIF or SUM with nested IF's

7. File Size, nested Ifs and Sum

8. Summing several IF's in a column.

10. Small (JFS.IFS or HPFS.IFS AutoCheck) install bump, Solved!

11. nested ifs and end ifs and elses

13. Why there is a need of IFS and non-IFS LSP sockets?

14. Editing and Moviing from 6 If's to 7 Ifs (Nested)

15. IFs, ELSEs, END IFS -- proper construction

14 post • Page:**1** of **1**