SUMIF with dates

[NOtoUCE]

A checklist of insect names contains 5 columns labelled Order, Family,
Genus, Species, Date. The first four columns are complete and the Date
column is to be filled in as insects are collected and identified. I want to
keep a running total of how many insects of each Order I've collected (e.g.
Coleoptera-291, Diptera-215, etc.)

When I do SUMIF(A:A,"Coleoptera",E:E) I get a huge and very wrong result
because Excel stores dates as numbers. There must be some easy way to do
this. Thanks for any help.

 

[Arvi Laanemets]

Hi

=SUMPRODUCT((A2:A1000="Coleoptera")*(E2:E1000<>""))

NB! You can't refer to whole column (like A:A) with sumproduct!

 

[Bob Phillips]

You need COUNTIF not SUMIF

=COUNTIF(A:A,"Coleoptera")

--

HTH

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

 

[Andrew]

Hi there,

Do you want to see see how many insects you caught on a particula
date? I'm not sure which column you are using to show the quantity s
let's assume you are using column F and you want to see how man
Coleoptera you caught on a certain date that you enter into anothe
cell, let's say G1.

Enter this formula into somewhere like G2

=SUM(IF(A1:A100="Coleoptera",IF(E1:E100=G1,F1:F100)))

and then push Ctrl, Shift & Enter simultaneously to make it an arra
formula.

Note: If you try to use entire columns, you will get a #NUMBER error

 

[Ron Rosenfeld]

A checklist of insect names contains 5 columns labelled Order, Family,
Genus, Species, Date. The first four columns are complete and the Date
column is to be filled in as insects are collected and identified. I want to
keep a running total of how many insects of each Order I've collected (e.g.
Coleoptera-291, Diptera-215, etc.)

When I do SUMIF(A:A,"Coleoptera",E:E) I get a huge and very wrong result
because Excel stores dates as numbers. There must be some easy way to do
this. Thanks for any help.

If each insect collected is on its own line, then perhaps something like:

=COUNTIF(A:A,"Coleoptera")

would be more appropriate.


--ron

 

[NOtoUCE]

Thanks to Arvi for the SUMPRODUCT suggestion (which worked), and thanks to
Bob and Ron for the simpler COUNTIF suggestion (which also worked).

Robin

 

[NOtoUCE]

Oops. I should have said that only Arvi's solution worked because it didn't
include the Coleoptera entries for which I have no specimen, whereas the
COUNTIF suggestion merely counted all instances of "Coleoptera" in the list,
many of which I haven't collected yet (and therefore do not want to count).
Thanks, Arvi!

Robin