# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  COUNTIF AND Statement

## LLori998

Hello,

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

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???????

----------


## 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

----------


## LLori998

> 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...  :Wink:  or the --'s at the beginning... interesting, what do those stand for??? 

thanks again, you're a lifesaver!!

----------


## daddylonglegs

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"))

----------


## bugmcw

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?

----------


## 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")

----------


## bugmcw

> 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

----------


## 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.

----------


## bugmcw

> 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-

----------


## NBVC

We prefer you do the search first too... but if you need to ask a question post a new thread and provide a link it becomes less of a confusion in the end...

----------


## ic31420

> 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?

----------


## arthurbr

Hi and welcome to the board

_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.*

----------

