COUNTIF Using two Criteria

[cleggy621]

Hi

I've had a search around the site without much luck.
I'm trying to count up a number of "won" estimates during a certain date period.
I have attached a simplified example of my data sheet.
Each row shows an amount of data entered for each contract, including the date and whether it was won or lost.
I want to count up the number of won estimates by cross referencing against the date so i can see how many were won during a certain month.

Cheers
Mark

[Fotis1991]

Hi

One way, is this formula.

Please Login or Register  to view this content.

[zbor]

Try:

=SUMPRODUCT(--(MONTH($C$7:$C$16=1)),--($F$7:$F$16="won"))

[cleggy621]

Thanks for the quick response.

While this solution works on my simplified sheet, when i try it on the more complex sheet i keep getting a #VALUE! error.
The only difference i can see on the other sheet is that the dates and won lost column are in data lists.
Would this cause this problem?

Mark

[Cutter]

Alternative

=COUNTIFS(C7:C16,">=12/1/1",C7:C16,"<12/2/1",F7:F16,"won")

[Fotis1991]

I don't understand the reason for this...

Would you like to upload a small sample worksheet, that is the same with your original?

[cleggy621]

Thanks Cutter, i managed to get it to work that way.

The problem i have is i'm no expert and i've been asked to mod a sheet that someone else set up to do a few extra things.
I'm finding more and more problems as i go along.

Can i ask is it possible to just sum/count, visible cells on a filterd list?

This is my sheet attached with the sensitive data removed.
Its basically a master sheet, with tabs that reference it to show monthly figures.

The cells highlighted in blue on Nov 2011 tab are the ones i am struggling with.

L9/10/11 just need to add the monthly totals and i cant work out how to get the date reference into these.
G15 needs to count column Q but ignore pending/estimating and void.
G16 needs to count as G15 but only return the number won.

I understand this is a big ask and is probably a mess from the beginning but any help is much appreciated.

Thanks

Mark

[zbor]

Can i ask is it possible to just sum/count, visible cells on a filterd list?

Instead of SUM formula write this: =SUBTOTAL(109,K22:K340) (same for other cells)

[Cutter]

Here is a link to the various forms of the SUBTOTAL() function.

[Haseeb Avarakkan]

Hi Mark,

In L9,

Please Login or Register  to view this content.

Copy to L10 & L11

G15,

Please Login or Register  to view this content.

G16,

Please Login or Register  to view this content.

better to use dynamic range, instead of hard coding the range

[daddylonglegs]

For that first one you could possibly shorten by using this

=SUMPRODUCT(SUBTOTAL(9,OFFSET(N$22,ROW(N$22:Q$1000)-ROW(N$22),))*(Q$22:Q$1000=K9))

[Haseeb Avarakkan]

Yes DLL

[cleggy621]

Thanks for all your help!
Done the job.
Much Appreciated.

However, I'm getting circular references in cells G15/16, L9/10/11 is this something to worry about or not?

Cheers

Mark