SUMIFS with ISNUMBER(SEARCH

azad092

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
Dear Members
to calculate a specific value I want to use the following formula
=SUMPRODUCT(--((ISNUMBER(SEARCH("MA-02",MTN!M2:M90000))))) its working perfectly
but I want to do this with tow conditions ie with this part of coding
,MTN!M2:M90000,MTN!$B$2:$B$90000,"="&'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
and I also change the SUMPRODUCT into SUMIFS
the formula change into this shape but its not working and giving an error

=SUMIFS(--((ISNUMBER(SEARCH("MA-02",MTN!M2:M90000,MTN!$B$2:$B$90000,"="&'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
If someone knows about this please help me
thanks
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,981
Office Version
  1. 365
Platform
  1. Windows
Why SUMIFS? It is better if you tell us what you want to do as well as how you're trying to do it. Your formula looks like you're trying to count, not sum, which means that you need COUNTIFS.

I think that this is what you're trying to do.

=COUNTIFS(MTN!$M$2:$M$90000,"*MA-02*",MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
 

azad092

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
hi jasonb
your idea is great, you understand my issue very clearly and resolved it very well
thanks alot
 

azad092

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
Why SUMIFS? It is better if you tell us what you want to do as well as how you're trying to do it. Your formula looks like you're trying to count, not sum, which means that you need COUNTIFS.

I think that this is what you're trying to do.

=COUNTIFS(MTN!$M$2:$M$90000,"*MA-02*",MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3)
hi jasnob
I have applied your suggested coding but I found an issue
actually this part of coding(,MTN!$B$2:$B$90000,'G Report'!$Z$3,MTN!$B$2:$B$90000,"<="&'G Report'!$AA$3) is a range of date i.e I enter the date in column B and in G Report column Z is a start date and AA is a end date
the formula match the start and end date of Column B and then calculate values.
but issue is that it works for one date
for example if I make 10 entries of date 22/7/20 and 5 entries of date 23/7/20
and I enter date in column Z 22/7/20 and AA as 23/7/20, it calculates only date 22, it not includes the date 23
=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,"="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
Date Start Date End Date
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,981
Office Version
  1. 365
Platform
  1. Windows
I thought that the criteria in your formula looked wrong, but as you hadn't told us what it should be doing, I only corrected the syntax of the part that would have been causing the error.

The very slight change below (in red) should fix it.

=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,">="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
 

azad092

Board Regular
Joined
Dec 31, 2019
Messages
152
Office Version
  1. 2007
Platform
  1. Windows
I thought that the criteria in your formula looked wrong, but as you hadn't told us what it should be doing, I only corrected the syntax of the part that would have been causing the error.

The very slight change below (in red) should fix it.

=COUNTIFS(MTN!$G$2:$G$90000,"RTA",MTN!$B$2:$B$90000,">="&$Z$3,MTN!$B$2:$B$90000,"<="&$AA$3)
Hi jasnob
good morning
actually I was confused about the mathematical operators, but your guidelines are useful and valueable
thanks dear
 

Watch MrExcel Video

Forum statistics

Threads
1,123,331
Messages
5,601,006
Members
414,420
Latest member
Richie77777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top