Counting, 2 criteria

[GD]

Hi, if i'm looking for a formula which will total the cumulative values in
cells Q3:Q3000, but only if the criteria in two other sets of cells are
present, ie A3:3000 has a date in the month of January and P33000 has the
text "KT" in it, how would I go about this?
The values in Q3:3000 are in pounds, so i'd be looking for a cumulative
figure if the preivous two criteria are met in other cells.

Cheers

 

[Ron Rosenfeld]

Hi, if i'm looking for a formula which will total the cumulative values in
cells Q3:Q3000, but only if the criteria in two other sets of cells are
present, ie A3:3000 has a date in the month of January and P33000 has the
text "KT" in it, how would I go about this?
The values in Q3:3000 are in pounds, so i'd be looking for a cumulative
figure if the preivous two criteria are met in other cells.

Cheers

Perhaps something like (not tested):

=SUMPRODUCT((MONTH(A3:A3000)=1)*(P33000="KT")*Q3:Q3000)
--ron

 

[GD]

Thanks Ron, i've amended it to be relevant to my sheets..

=SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008
Errors'!P33000="KT")*'2008 Errors'!Q3:Q3000)

as it's referring to another sheets cells, and unfortunately it's returning
a result of #VALUE!

Any ideas?

 

[Bob Phillips]

Does any of Q3:Q3000 have text values?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[GD]

No just numerical values

Does any of Q3:Q3000 have text values?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

Thanks Ron, i've amended it to be relevant to my sheets..

=SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008
Errors'!P33000="KT")*'2008 Errors'!Q3:Q3000)

as it's referring to another sheets cells, and unfortunately it's returning
a result of #VALUE!

Any ideas?

Most likely, your data is not in the expected format. If all of the values in
col Q are numeric, then I'll guess there's an issue with your DATE column where
one or more entries are not "real Excel dates".

You can probably sort this out by, on your 2008 Errors worksheet, take an
unused column and, in row 3, enter =month(a3). Fill down to a3000 and see if
any of the cells return a #VALUE!.


--ron

 

[GD]

Hi, did what you suggested and all fields return a figure aside from S3 and
S13 (Which correspond to A3 and A13 which are the first to cells with dates
in) - the strange thing is ive copied dates from other cells which are
returning a figure no problem, into them, and it still reflects a #VALUE! in
the S3 & S13. Both are also set in date format with the correct layout

 

[Ron Rosenfeld]

Hi, did what you suggested and all fields return a figure aside from S3 and
S13 (Which correspond to A3 and A13 which are the first to cells with dates
in) - the strange thing is ive copied dates from other cells which are
returning a figure no problem, into them, and it still reflects a #VALUE! in
the S3 & S13. Both are also set in date format with the correct layout

With the problem only being in two cells, I would adjust them manually.

FIRST, make sure the cell is formatted as GEneral or as Date.
SECOND, re-enter the date manually (typing it in).
--ron