Hi,

I have a spreadsheet of all attendees of past events. I now have to add to
it anyone who has registered for an event (but did not show up). I am
differentiating between these in column Z - all those who registered but did
not show up have a "R" marked; attendees are left blank. If my original
formula is: =COUNTIF('Full list'!D:D,3), how do I add to it now to only
COUNTIF if there IS NOT a "R" marked in column Z?

Thanks so much.

Hi - sounds like you just need a simple subtraction:

=COUNTIF('Full list'!D:D,3) - COUNTIF('Full list'!Z:Z,"R")

If the data is not as simple as that, you could use a SUMPRODUCT formula:

=SUMPRODUCT(('Full list'!D:D=3)*('Full list'!Z:Z<"R"))

HTH
--
Rgds, Geoff

"A crash reduces
Your expensive computer
To a simple stone"


"h20polo" wrote:

Hi,

I have a spreadsheet of all attendees of past events. I now have to add to
it anyone who has registered for an event (but did not show up). I am
differentiating between these in column Z - all those who registered but did
not show up have a "R" marked; attendees are left blank. If my original
formula is: =COUNTIF('Full list'!D:D,3), how do I add to it now to only
COUNTIF if there IS NOT a "R" marked in column Z?

Thanks so much.

You could subtract those where there is an "R" in column Z, i.e.:

=COUNTIF('Full list'!D:D,3) - COUNTIF('Full list'!Z:Z,"R")

but this assumes that "R" is entered in column Z only against those
who also have a 3 in column D.

Hope this helps.

Pete

On Jun 19, 11:38 am, h20polo
wrote:
Hi,

I have a spreadsheet of all attendees of past events. I now have to add to
it anyone who has registered for an event (but did not show up). I am
differentiating between these in column Z - all those who registered but did
not show up have a "R" marked; attendees are left blank. If my original
formula is: =COUNTIF('Full list'!D:D,3), how do I add to it now to only
COUNTIF if there IS NOT a "R" marked in column Z?

Thanks so much.

Thanks Pete, but "R" isn't only entered in column Z against those who also
have a 3 in column D - I have 10 different categories in column D, so
subtracting the "R" makes the number too low. I tried xlbo's SUMPRODUCT
formula, but got a #NUM error. Any suggestions?

Thanks!

"Pete_UK" wrote:

You could subtract those where there is an "R" in column Z, i.e.:

=COUNTIF('Full list'!D:D,3) - COUNTIF('Full list'!Z:Z,"R")

but this assumes that "R" is entered in column Z only against those
who also have a 3 in column D.

Hope this helps.

Pete

On Jun 19, 11:38 am, h20polo
wrote:
Hi,

I have a spreadsheet of all attendees of past events. I now have to add to
it anyone who has registered for an event (but did not show up). I am
differentiating between these in column Z - all those who registered but did
not show up have a "R" marked; attendees are left blank. If my original
formula is: =COUNTIF('Full list'!D:D,3), how do I add to it now to only
COUNTIF if there IS NOT a "R" marked in column Z?

Thanks so much.