Thread: COUNTIF AND Statement

Hello,

I'm drawing blank on what i think should be a simple formula... can someone help me?!

I have a spreadsheet that has a list of values that i'm trying to count if one column = "A" and the other column = "Dec".

So, i would think the formula would read something like this:

=COUNTIF(AND(Column B="A",Column B="Dec")

but of course, it's not working... the next formula would count if one column = B and the other column = Dec

Anyone???????

For multicondition counting, if you're not using Excel 2007, use SUMPRODUCT like this

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Dec"))

Note: I'm assuming column B contains text, not formatted dates

Originally Posted by daddylonglegs

For multicondition counting, if you're not using Excel 2007, use SUMPRODUCT like this

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Dec"))

Note: I'm assuming column B contains text, not formatted dates

you are AWESOME! i've never heard of the sum product formula... or the --'s at the beginning... interesting, what do those stand for???

thanks again, you're a lifesaver!!

The parts of the formula like

A1:A100="A" produce arrays of TRUE/FALSE values. The -- coerces these to 1/0 values and SUMPRODUCT calculates with these. Some people prefer this syntax

=SUMPRODUCT((A1:A100="A")*(B1:B100="Dec"))

OK, this answered my question.... ALMOST

i have it as:

=SUMPRODUCT(--('Sheet1'!$E$16:$E$15000=7311),--('Sheet1'!H16:H15000="Shampoo"))

which returns::: 60

Which is correct.... however, i want to add the numbers in Column E to get my total, not just sum all of the records where both of those are correct....

In english i want the formula to do this:

SUM 'Sheet1'!E16:E15000 (this column is the number of shampoos bought by an individual) ONLY IF 'Sheet1'!$E$16:$E$15000=7311 AND 'Sheet1'!H16:H15000="Shampoo"

any ideas?

Originally Posted by daddylonglegs

Sum range can't be column E, you're already using that? Assuming you want to sum column F try

=SUMPRODUCT(--('Sheet1'!$E$16:$E$15000=7311),--('Sheet1'!H16:H15000="Shampoo"),'Sheet1'!$F$16:$F$15000)

although in Excel 2010 you can use SUMIFS

=SUMIFS('Sheet1'!$F$16:$F$15000,'Sheet1'!$E$16:$E$15000,7311,'Sheet1'!H16:H15000,"Shampoo")

I did indeed mean to Sum F... you are a saint... thank you SO much for your quick reply

Originally Posted by NBVC

bugmcw.... next time please read the forum rules....

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Many apologies, most forums i go to get very angry when people dont first search for their answer, and try to find relevant information therein. Will do next time-

Originally Posted by daddylonglegs

For multicondition counting, if you're not using Excel 2007, use SUMPRODUCT like this

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Dec"))

Note: I'm assuming column B contains text, not formatted dates

Sorry to bump this one, but i dont think my question really warrents a new thread...

I am trying to do the same, but one of my columns contains a date, can anyone help?