Count IF command

[NikeGuy23]

Hi I have an excel sheet with Data on sheet1. On Sheet2 I am trying to get it to do calculation. I am having a problem with the countif command. I need to count the number of records that meet a few criterial. I.E.
=COUNTIF(Sheet1!F:F,"Die Set 7010-38 38 DSS")
How do I add it to check another criterial. Like right now it is checking F:F to make sure it equals that Die Set, I need it to check another column to to and only count those that match those two. Any ideas?

 

[Lilliabeth]

=SUMPRODUCT((F1:F65535="die set something")*(G1:G65535="oranges"))

This doesn't check row 65536.

 

[NikeGuy23]

Thanks Lillabeth, but the Data is on sheet1 so I am assuming that the formula would be:
=SUMPRODUCT((Sheet1!F1:F30000,"Die Set 7010-38 38 DSS")*(Sheet1!BG1:BG30000,"TRUE"))

But I get an error when this happens. And all I need it to do is count and display the number of records that meet those two criteria.

 

[xlbo]

=SUMPRODUCT((Sheet1!F1:F30000 = "Die Set 7010-38 38 DSS")*(Sheet1!BG1:BG30000 ]="TRUE"))

 

[xlbo]

=SUMPRODUCT((Sheet1!F1:F30000 = "Die Set 7010-38 38 DSS")*(Sheet1!BG1:BG30000 ="TRUE"))

 

[Lilliabeth]

Gee, not sure where my head was as I was typing. Yep, the equal signs are rather important.

 

[NikeGuy23]

Ok well that is not giving me an error however it is reporting the wrong answer. It should be 5 but is showing up as 0.

 

[xlbo]

you have some data issues then

you may have:

"Die Set 7010-38 38 DSS "
rather than
"Die Set 7010-38 38 DSS"

check your data 1st

 

[Lilliabeth]

I suspect your data has spaces in it that you aren't taking into account.

But the question was not entirely clear, so your needs may have been misunderstood. You did want to count rows where both criteria are true, right?

Create a blank workbook and test the formula on clean data. Take you 2 minutes.

 

[NikeGuy23]

Ok I tried the formula on a new sheet. In Cell A1 I put Test in Cell B2 I put in TRUE. I then did the following formula
=SUMPRODUCT((Sheet1!A1 = "Test")*(Sheet1!B1 = "TRUE"))

I get 0 as an answer.

 

[NikeGuy23]

Sorry in Cell B1 I put TRUE.
All I am trying to do is count the number of times it meets this criteria and give me the number in a cell. So if it meets both criteria 5 times return a value of 5.

 

[xlbo]

that's the problem with TRUE / FALSE - sometimes excel doesn't know whether to treat them as text (ie "TRUE") or boolean values (ie TRUE)

try this:
=SUMPRODUCT((Sheet1!A1 = "Test")*(Sheet1!B1 = TRUE))

 

[Lilliabeth]

Or try it with apples and oranges!

 

[NikeGuy23]

PERFECT...That worked THANK 100000000000000.

 

[NikeGuy23]

One last question sorry Can I have more than one * I.E.
=SUMPRODUCT((Sheet1!A1 = "Test")*(Sheet1!B1 = TRUE)*(Sheet1!C1 = FALSE))

 

[xlbo]

yes - but why didn't you just try it ???

 

[AnotherHiggins]

NikeGuy23, you might want to consider awarding some of those little purple stars to xlbo &/or Lilliabeth for helping you out.

clicking the " Thank _____ for this valuable post!" link under any post will do the trick.

This is not only the way we say 'Thanks' around here, it also marks the thread as resolved so that future visitors who might have a problem similar to yours know that this is a thread containing an answer.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.