Solved: Multiple Counif() and Sumif() functions

[skaswani]

Hello

this is my code : =COUNTIF('SCN Site Format'!M:M,'Branch- Summary'!A7) and it is working fine, but what i need to do is to count only those records whom are marked "PAID" on

Sheet : SCN Site Format

Coloumn Name : O

same with Sumif()

=SUMIF('SCN Site Format'!M:M,'Branch- Summary'!A7,'SCN Site Format'!J:J)

same with this also, i want only Paid data in column O in SCN Site Format Sheet

please guide me

thanks,

[Bob Phillips]

=SUMPRODUCT(--('SCN Site Format'!M1:M100='Branch- Summary'!A7),
--('SCN Site Format'!O1:O100="Paid"))

and

=SUMPRODUCT(--('SCN Site Format'!M1:M100='Branch- Summary'!A7),
--('SCN Site Format'!O1:O100="Paid"),'SCN Site Format'!J1:J100)

Note that SUMPRODUCT doesn't work with complete columns prior to Excel 2007, you have to specify a range.

[Bob Phillips]

Of, if using XL2007, you can use

=SUMIFS('SCN Site Format'!J:J,'SCN Site Format'!M:M,'Branch- Summary'!A7),'SCN Site Format'!O:O,"Paid")

[skaswani]

=SUMPRODUCT(--('SCN Site Format'!M:M='Branch- Summary'!A7),--('SCN Site Format'!O:O="Paid"))

error:

#NUM!

[Bob Phillips]

Is that what I posted? Read it again.

[skaswani]

sorry, i modify that as per my need, but i forgot to put a comma,


what about the sumif()

how will i apply that?

thanks,

[Bob Phillips]

I gave you that too.

[skaswani]

ya.. i thaught both are same..

thanks!