Mutiple COUNTIF or equivalent.

[noalenpell]

What I want to do is fairly complicated.

I have Sheet 1 with data on,
then Sheet 2 with the figures of this data.

On Sheet2;
In one column im using COUNTIF to count Column A on Sheet 1 (which is
a date) to see how many were entered on this date.
On sheet 1 i have another column which is kind of like a tick column,
where some are ticked to see if this query has been resolved.
I want to use a countif to count these 'ticks' (which are the letter
'a') but to only count them on the date given.

IE. 2 Example lines from Sheet 1..
DATE | TITLE | TICK |
31/3/2008 | COMPANY A | |
31/3/2008 | COMPANY B | a |
28/3/2008 | COMPANY C | |

On Sheet 2 this would show as...
DATE | INPUT | TICK |
28/3/2008 | 1 | |
31/3/2008 | 2 | |

With the cell for the input being;
=(COUNTIF(Sheet1!B:B,A34))
[with the references being relevant to be spreadsheet]

I want the tick box to show how many have 'a' present in that column,
creating this..
DATE | INPUT | TICK |
28/3/2008 | 1 | |
31/3/2008 | 2 | 1 |

I tried using countif again, but it counts ALL the ticks for every
date. This isnt what i want.
Can anyone help??

 

[Pete_UK]

You will need to use SUMPRODUCT rather than COUNTIF, as you have more
than one criteria. I'm not sure what columns your data occupies, but
it will be something like this:

=SUMPRODUCT((Sheet1!A$2:A$100=A34)*(Sheet1!C$2:C$100="a"))

I've assumed your data occupies 100 rows - adjust to suit, but you
cannot use full-column references in versions before XL2007.

Hope this helps.

Pete

 

[noalenpell]

You will need to use SUMPRODUCT rather than COUNTIF, as you have more
than one criteria. I'm not sure what columns your data occupies, but
it will be something like this:

=SUMPRODUCT((Sheet1!A$2:A$100=A34)*(Sheet1!C$2:C$100="a"))

I've assumed your data occupies 100 rows - adjust to suit, but you
cannot use full-column references in versions before XL2007.

Hope this helps.

Pete

What I want to do is fairly complicated.

I have Sheet 1 with data on,
then Sheet 2 with the figures of this data.

On Sheet2;
In one column im using COUNTIF to count Column A on Sheet 1 (which is
a date) to see how many were entered on this date.
On sheet 1 i have another column which is kind of like a tick column,
where some are ticked to see if this query has been resolved.
I want to use a countif to count these 'ticks' (which are the letter
'a') but to only count them on the date given.

IE. 2 Example lines from Sheet 1..
   DATE      |        TITLE         | TICK |
31/3/2008   |   COMPANY A   |         |
31/3/2008   |   COMPANY B   |    a   |
28/3/2008   |   COMPANY C   |         |

On Sheet 2 this would show as...
   DATE      |  INPUT  | TICK |
28/3/2008   |     1       |         |
31/3/2008   |     2       |         |

With the cell for the input being;
=(COUNTIF(Sheet1!B:B,A34))
[with the references being relevant to be spreadsheet]

I want the tick box to show how many have 'a' present in that column,
creating this..
   DATE      |  INPUT  | TICK |
28/3/2008   |     1       |         |
31/3/2008   |     2       |    1   |

I tried using countif again, but it counts ALL the ticks for every
date. This isnt what i want.
Can anyone help??- Hide quoted text -

- Show quoted text -

Oh really? I didnt know that, well it never goes over 2000, so i can
program it up to that - its just a BETA at the moment though, so i
wont to test it. I didnt realise you cant use full column references,
its strange you say that cos my other formula, the original COUNTIF
works with a full column reference??
Anyway, I will give this ago - thanks.

 

[Pete_UK]

You can't use a full-column reference with the SUMPRODUCT function
(and with array formulae), but you can with COUNTIF and SUMIF (and
many others).

Pete

 

[noalenpell]

You can't use a full-column reference with the SUMPRODUCT function
(and with array formulae), but you can with COUNTIF and SUMIF (and
many others).

Pete



- Show quoted text -

This worked brilliantly, thankyou.
Now I want to Add the values of Column D - which is the money of each
entry, but only if it is 'ticked'... so basically I want to use the
formula you have provided, then tell it that "where this applies -
SUM of Column D" .. if that makes sense??
Thanks if you can help...

 

[Pete_UK]

I've been away a few days, but if you are still monitoring this
thread, then using my first formula as a basis, you can amend it to
this:

=SUMPRODUCT((Sheet1!A$2:A$100=A34)*(Sheet1!C$2:C$100="a")*(Sheet1!D$2
$100))

This will give you a SUM of column B in Sheet1 where column C = "a"
AND column A = A34 in the summary sheet.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, that should have said:

This will give you a SUM of column D in Sheet1 where column C = "a"
AND column A = A34 in the summary sheet.

Pete

 

[NPell]

Sorry, that should have said:

This will give you a SUM of column D in Sheet1 where column C = "a"
AND column A = A34 in the summary sheet.

Pete





- Show quoted text -

Thanks very much!! Cheers

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete