SUMIF & IF

G

GEM

On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe)
and on column C I have ammounts ($0.00).

On a seperate worksheet inside the same workbook, I would like to sum the
ammounts per name, if they equal to =Today().

For example,

A B C
1 July 20, 2009 John Doe $20
2 July 20, 2009 Jane Doe $10
3 July 28, 2009 John Doe $5
4 July 28, 2009 John Doe $20
5 July 28, 2009 Jane Doe $30
6 July 28, 2009 Jane Doe $10

July 28, 2009 ( =Today() )
John Doe - $25
Jane Doe - $40


Hope I got my idea through....
 
P

Pete_UK

Assuming you have today's date in A1 of Sheet2, and then names below
starting in A2, put this formula in B2:

=SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$100=A2),Sheet1!C$1:C
$100)

Adjust to suit your data - I've assumed 100 rows. Copy down for as
many individual names as you have in Sheet2.

Hope this helps.

Pete
 
S

Shane Devenshire

Sheet1!A$1:A$99Hi,

In 2003:

=SUMPRODUCT(--(Sheet1!A$1:A$99=Today()),--(Sheet1!B$1:B$99=A1),Sheet1!C$1:C$99)

In 2007:

=SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,Today(),Sheet1!B$1:B$99,A1)

where the names are in A1:A10 ...

If you want you can enter =TODAY() in B1 and then replace it in the above
formulas with B$1.

=SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,B$1,Sheet1!B$1:B$99,A1)
 
G

GEM

Is there a way to count this?? For example,

John Doe 2
Jane Doe 2

Because each one appeared 2 times today.
 
G

GEM

Is there a way to count this?? For example,

John Doe 2
Jane Doe 2

Because each one appeared 2 times today.
 
P

Pete_UK

If you want a count of the names that appeared today, just miss off
the final term, i.e.:

=SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$100=A2))

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top